/*-------------------------------------------------------------------\
| DDE: Normally, when we output to MS word, we will write to the most|
| recent active word window, this is very easy to generate some |
| errors when we have many word window open. Here is a trick to get |
| avoid this problem: define the file path to be updated, use command|
| to force SAS to output; |
\-------------------------------------------------------------------*/
FILENAME _wsys_ dde "winword|Y:\CLINICAL\TACHY\BIOSTAT\Duo\Projects\VR-IDE\
Doc\VRIDE_report_draft 0.1 19Feb2002.doc" command lrecl=1048576;
%fillup(oldbmark='PatIDImpUnVisit', indata=one, var=x, wordref=_wsys_);
/*-------------------------------------------------------------------\
| Requesting more memory for interactive sessions |
| xsas8 -memsize 128m & |
\-------------------------------------------------------------------*/
/*-------------------------------------------------------------------\
| SAS Useful functions: |
| 1) write numeric variables in word format; |
| 2) just capitalize the first character of the sentence; |
| 3) Capitalize the first character of each words in a setence; |
| 4) replace a substring with another string: |
| tranwrd (string, replace, with) |
| 5) '09'x: tab (09 is hexadecimal values for '\t'); |
| '0D'x: return sign (hexadecimal value for '\n'); |
\-------------------------------------------------------------------*/
/* options ps=max ls=1 skip=min;*/
data one;
infile datalines delimiter=',' dsd missover;
/*delimiter='09'x for 'Tab' delimited data*/;
input x y cpevent $21.;
datalines;
1.1, 1.2, 'OneTwo',
12, 132, tHIS is AN EXAMPLE,
1.1, 2.3, 1 MONTH FOLLOW-UP,
2.1, 232, 4 MONTH FOLLOW-UP,
1.3, 4.1, 7 MONTH FOLLOW-UP,
4.2, 100, UNSCHEDULED FOLLOW-UP
;
data two;
set one;
wordnum1=put(x, words30.);
wordnum2=put(y, wordf30.);
Up1low=upcase(substr(wordnum1,1,1))||lowcase(substr(wordnum1,2,(length(wordnum1)-1)));
trstr=tranwrd(str,"'",'"');
drop x y str;
run; %prt(two);
/* Cocatenate a '\n' with the currecnt variable */
data two2;
set one;
length newcp $22. lastchar char $1.;
lastchar=' ';
newcp=' ';
do i=1 to length(cpevent);
char=substr(cpevent,i,1);
if (lastchar=' ' or i=1) then do;
char=upcase(char);
newcp=compbl(newcp)||'0D'x||char;
end;
else do;
char=lowcase(char);
newcp=trim(left(newcp))||'0D'x||char;
end;
lastchar=char;
end;
cpevent=newcp;
drop lastchar i char;
run;
%print(two2);
/*-------------------------------------------------------------------\
| DDE Session: Close and Reopen a document when the memory is not |
| enough; |
\-------------------------------------------------------------------*/
data one;
do x=1 to n;
run;
%sysexec 'C:\Program Files\Microsoft Office\Office\WINWORD.EXE';
data _null_;
file wordsys;
set one;
format str $2000.;
if mod(x, 3)=0 then do;
str='[FileSaveAs .Name = "'||trim(left("D:\TEMP\temp.doc"))||'"]';
put str;
put '[FileClose]';
str=sleep(2);
call system('D:\TEMP\temp.doc');
str=sleep(2);
str='[EditGoTo .Destination = "'||trim(left("t1"))||'"]';
put str;
put '[TableSelectTable]';
put '[EndofLine]';
put '[TableSelectRow]';
put '[NextCell]';
end;
str= '[Insert "'||trim(left(put(x, 3.)))||'"]';
put str;
put '[NextCell]';
run;
/*-------------------------------------------------------------------\
| 1). Input date values: |
| '01jan1901'd; 01/01/1901 |
| '17OCT1991:14:45:32'DT 32 seconds past 2:45 p.m., 17 October 1991|
| '17OCT1991:12:5'DT 12:05 p.m., 17 October 1991 |
| '17OCT1991:2:0'DT 2 AM, 17 October 1991 |
| '17OCT1991:0:0'DT midnight, 17 October 1991 |
| CAUTION: |
| You can only compare equivalent literals against SAS date, time or |
| datetime values since they each have a different unit of measure. |
| For example, you cannot compare a SAS data set value that has been |
| defined with a datetime format against a date literal using |
| select * where hiredate = {d'1995-01-02'} |
| / or |
| select * where hiredate = '02jan1995'd |
| Instead, use a datetime literal such as |
| select * where hiredate = {ts'1995-01-02 00:00:00'} |
| / or |
| select * where hiredate = '02jan1995:00:00:00'dt |
| |
| 2). calculate the actual date, month or year difference; |
| 3). Date format: mm/dd/yy -- mmddyy10.; |
| mmddyy -- mmddyy6.; |
| ddmonyyy -- date9.; |
| time format: 14:45:32 -- time12.3.; |
\-------------------------------------------------------------------*/
data three;
start=mdy(1,3,1967);
end=mdy(6,5, 2001);
newdt='01jan1901'd;
daysreal = datdif(start, end, 'actual'); /*'ACT/ACT' or 'ACTUAL' returns the actual number of days */
daysappr = datdif(start, end, '30/360'); /*'30/360' or '360' returns the number of days assuming a 30-day month and 360-day year */
yrsreal = yrdif(start, end, 'actual'); /*'ACT/ACT' or 'ACTUAL' returns the actual number of years */
mthsreal = yrsreal * 12;
mthmanual=(end-start)/30.25;
yrsappr = yrdif(start, end, 'act/365'); /*'ACT/365' returns the number of years as the actual number of days divided by 365*/
mthsappr = yrsappr * 12;
/* a.. a numeric expression - interpreted as missing if it is ordinary missing (.) or contains special missing characters (._, .A to .Z) */
/* b.. a character expression - interpreted as missing if it contains all blank spaces */
if missing(var1) then do;
/* perform corrective action when var1 missing;*/
end;
run;%prt(data=three, var=mthsreal mthmanual);
/********************************************************************/
/****************** User-Defined Character Classes ******************/
/*See: http://www.ncsu.edu/it/sas/help/lgref/z0331192.htm#zid-1554 */
/********************************************************************/
/*-------------------------------------------------------------------\
| 1) Select only the first and the last observations; |
| |
| 2) User-defined Character Classes |
| If the character variable contains missing values of non-numeric |
| data then the value of new_num will be missing. When the character |
| variable contains non-numeric data, an 'invalid argument' note will|
| be written to the log. This note can be suppressed using the ?? |
| format modifier |
| newc=input(b, ?? best4.); |
| |
| 3) Convert character variables to numeric variables when possible; |
\-------------------------------------------------------------------*/
data t1;
input a b $;
cards;
01 A
02 b
03 c
04 d
05 -9
06 1
07 0
;
run;
data t2;
do i=1,n;
set t1 point=i nobs=n;
output;
end;
stop;
run; %prt(t2);
data temp;
set t1;
/** Change all quoted numeric to numeric values **/
if (rxmatch(rxparse("$d"),b)>0) then newb= b+0;
/** exclude all quoted numeric values **/
if (rxmatch(rxparse("$a"),b)) then newchar=b;
/** find only those which started with 1-9 numbers **/
if (rxmatch(rxparse("$d"),b)=1) then newb1=b+0;
newc=input(b, ?? best4.);
/* The following RXPARSE tells SAS to replace any uppercase */
/* letter with a space followed by that same letter. This will */
/* cause a leading space in the finished variable. */
if _n_=1 then rx=rxparse("<$u> TO ' ' =1");
/* The 5 in the RXCHANGE below represents the number of times we */
/* will make this change you may want to increase it....it is */
/* best not to OVER estimate by too much as it will have a */
/* negative effect on perfomance. */
call rxchange(rx,5,x);
run;
/* Test if a variable starts with ('Mc' 'Mac') */
data test;
length y $20.;
y='Mac test'; output; y='mac test'; output; y='start mactest'; output; y='start mac test'; output;
run;
data curt;
set test END=eof;
retain rx;
if _n_=1 then rx=rxparse("` (mac|mc) [' ']$c*$w*");
match=rxmatch(rx,y);
if(match>0) then
put "Got a match, " y= match=;
else
put "Not a match, " y= match=;
if eof then call rxfree(rx);
run;
%print(curt);
/* Check the entire string to see if it consists of only integers */
data one;
_seed_="&seed";
_seedchk_=rxparse("` ($d)$d*");
call rxsubstr(_seedchk_,_seed_,_seedpos_,_matchlen_,_score_);
if (_matchlen_ < length(trimn(left(_seed_)))) then do;
put '==> Alert! Please provide an integer seed!';
abort;
end;
call rxfree(_seedchk_);
drop _seedchk_ _seedpos_ _matchlen_ _score_ _seed_;
run;
/*-------------------------------------------------------------------\
| 1) List the output of the procedure, and find out the name of the |
| output dataset / datasets; |
| 2) Output the dataset and change the name to temp1 |
| 3) print a dataset withint a datastep; |
| 4) User defined format; |
| How to check the user defined formats; |
| 5) How to change the length of a variable in a data set; |
| 6) Usage of 'file print ods;' and 'put _ods_;'; |
| 7) How to disregard user defined formats if these formats cannot be|
| be found; |
| 8) Mixed informats: characteristic and numeric together in one |
| column; |
\-------------------------------------------------------------------*/
ods trace on/listing; /* or ods trace on/label */
proc freq data=attest.covar;
table rassign;
ods output OneWayFreqs=temp1;
run;
ods trace off;
%prt(temp1);
options pagesize=60 linesize=64 nodate pageno=1;
title 'Leading Grain Producers';
/* User Defined formats */
proc format;
value $cntry 'BRZ'='Brazil'
'CHN'='China'
'IND'='India'
'INS'='Indonesia'
'USA'='United States';
value ridvstf 3='1 Month Follow-Up'
4='4 Month Follow-Up'
5='7 Month Follow-Up'
8='Unscheduled Follow-Up'
other = 'Unknown';
run;
/*How to disregard user defined formats if these formats cannot be */
libname winfd "T:\TACHY\BIOSTAT\Brady Programs\Unix\sara\4033\datasets";
options fmtsearch=(WINFD) NOFMTERR;
/* How to check the user customized formats */
proc format library=work fmtlib;
select @eval ridvstf;
run;
/* Mixed informats: characteristic and numeric together in one column; */
Proc format ;
invalue mixed ‘LOW’ = -99
1-10 = 1
11-20 = 2
‘BIG’ = 99
other = 0 ;
Run ;
data _null_;
length Country $ 3 Type $ 5;
input Year country $ type $ Kilotons;
format country $cntry.;
label type='Grain';
file print ods;
put _ods_;
datalines;
1995 BRZ Wheat 1516
1995 BRZ Rice 11236
1995 BRZ Corn 36276
1995 CHN Wheat 102207
1995 CHN Rice 185226
1995 CHN Corn 112331
1995 IND Wheat 63007
1995 IND Rice 122372
1995 IND Corn 9800
1995 INS Wheat .
1995 INS Rice 49860
1995 INS Corn 8223
1995 USA Wheat 59494
1995 USA Rice 7888
1995 USA Corn 187300
1996 BRZ Wheat 3302
1996 BRZ Rice 10035
1996 BRZ Corn 31975
1996 CHN Wheat 109000
1996 CHN Rice 190100
1996 CHN Corn 119350
1996 IND Wheat 62620
1996 IND Rice 120012
1996 IND Corn 8660
1996 INS Wheat .
1996 INS Rice 51165
1996 INS Corn 8925
1996 USA Wheat 62099
1996 USA Rice 7771
1996 USA Corn 236064
;
run;
/*-------------------------------------------------------------------\
| How to read in multiple records from one line of observations |
\-------------------------------------------------------------------*/
data one;
VTVF_start=5;
VTVF_end=8;
INDUCED='NO';
run;
data two;
set one;
do vtvfid=vtvf_start to vtvf_end;
output;
end;
run;%prt(two);
/*-------------------------------------------------------------------\
| How to read in plain text file |
\-------------------------------------------------------------------*/
filename testdata 'testdata.txt';
data test;
length recd $ 200 ;
infile testdata missover;
input @1 recd ;
recd = upcase(recd); /*** convert to upper case ***/
recd=tranwrd( recd, '=' , ' ' ); /*** convert = to blanks ***/
recd=tranwrd( recd, '&' , ' ' ); /*** convert & to blanks ***/
recd=tranwrd( recd, 'Q1', ' ' ); /*** convert Q1 to blanks ***/
recd=tranwrd( recd, 'Q2', ' ' ); /*** convert Q2 to blanks ***/
recd=tranwrd( recd, 'Q3', ' ' ); /*** convert Q3 to blanks ***/
q1 = input( scan( recd, 1), best.); /*** get 1st word and input ***/
q2 = input( scan( recd, 2), best.); /*** get 2nd word and input ***/
q3 = input( scan( recd, 3), best.); /*** get 3rd word and input ***/
run;
/*==================================================================*/
/* Create keys in a data step */
data serialinfo;
set lots;
set db2sys.disc_stat_history (dbkey=lot keep=lot trdate trtime resource) key=dbkey;
run;
/* concatenate */
%macro sqlconcat(indata=, var=, outvar=);
%global &outvar;
%local localv1 localv2;
ods listing close;
proc sql;
select distinct &var
into :localv1 separated by '", "'
from &indata;
select distinct &var
into :localv2 separated by '", "0'
from &indata;
quit;
%let localv1="&localv1";
%let localv2="0&localv2";
%let &outvar=(&localv1, "IJF200758S", "IJF200854S", "0IJF200758S", "0IJF200854S", &localv2);
%put &outvar is &&&outvar;
ods listing;
%mend sqlconcat;
%sqlconcat(indata=attest.pcsat500,var=serialno, outvar=allserial);
/*-------------------------------------------------------------------\
| Create a Error Free Monitor dataset, if there is an error, you can |
| delete this file, so that you can track the errors of your program.|
\-------------------------------------------------------------------*/
DATA _ErrFree%EVAL(1*&SYSJOBID);
RUN;
%let crtldir=%SYSFUNC(PATHNAME(work));
filename crtlref "&crtldir.\_ErrFree%EVAL(1*&SYSJOBID).sas7bdat";
data one;
crtl=fdelete('crtlref');
run;
/*-------------------------------------------------------------------\
| 1) Search SAS files from a catalog; |
| 2) Save 27% of time in regular sorting; |
| 3) Combining a datastep and sql by utilizing sas views, which can |
| save 40% of time; |
\-------------------------------------------------------------------*/
/* Search SAS files from a catalog; */
%macro catscan(cat,file) ;
proc build catalog=&cat batch ;
print source prtfile="&file" ;
run ;
dm 'fslist "&file"' fslist ;
%mend catscan ;
/*Combining datastep and proc sql */
data out / view=out ;
set sasuser.houses ;
if style='CONDO' then
put 'obs=' _n_ price= ;
run ;
proc sql ;
create table more as
select * from out
where price > 100000;
quit;
/* Efficient Way of Sorting */
Data test/view=test;
if … flag=1 ;
run;
Proc sort data=test;
by flag;
run;
/*-------------------------------------------------------------------\
| make SAS print the current time and date, rather than the |
| time and date when SAS was last started? |
\-------------------------------------------------------------------*/
%put Current system time is %sysfunc(datetime(), datetime.).;