| David Franklin, Blog | |
| 2008 Archive | |
| Back | dfranklinnh@gmail.com |
|
JanuaryWhen looking at a SAS program you are likely to see something like the following when dealing with text strings: rslt_string=TRIM(LEFT(original_string)); What this did was left align the text in the variable original_string and then remove any blanks after the last character. In SAS 9 a new function was introduced called STRIP that does the same task but with one function so the original code above can instead be written as: rslt_string=STRIP(original_string); If you are still using an older version of SAS you could do the same thing with a macro, using the following code: %MACRO STRIP(intxt); /*INTXT=text variable*/
TRIM(LEFT(&intxt))
%MEND STRIP;
rslt_string=%STRIP(original_string);
You may wonder why there is no ';' at the end of the code inside the macro definition? This allows for the macro to be called inside the code multiple times, as the following example demonstrates: rslt_string=%STRIP(original_string_A)||%STRIP(original_string_B); FebruaryI had an occasion recently to need negative values to to be displayed with a '()' around the value, rather than the more usual '-' in front of the value. Below is the code that was used to do this and a sample of its use: proc format;
picture negvals
low-<0='00000.0)' (prefix="(" )
0-high="00000.0" ;
run;
72 data _null_;
73 do pctchng="50" to 50 by 20;
74 dispval=put(pctchng,negvals.);
75 put pctchng= dispval=;
76 end;
77 run;
pctchng=50 dispval=50.0
pctchng=30 dispval=30.0
pctchng=10 dispval=10.0
pctchng=-10 dispval=(10.0)
pctchng=-30 dispval=(30.0)
pctchng=-50 dispval=(50.0)
MarchI sometimes want to get a listing of all the SAS programs for a particular directory. There are a large number of solutions available, but here is my favourite using PIPE within SAS: filename dircmd pipe "dir /b C:\TUAI\TABLES";
data dirfile;
length filelst $256;
infile dircmd missover length=len;
input filelst $varying246. len;
run;
AprilWhen data is transposed it is possible to put both a label onto the transposed variable inside the TRANSPOSE procedure using the IDLABEL statememt. The following code shows how this is done: DATASET: VITAL_IN
SUBJECT TEST TSTLABEL TSTVALUE
00-01 HGT Height (cm) 175
00-01 WGT Weight (kg) 75
proc sort data=vital_in;
by subject;
run;
proc transpose data=vital_in out=vital_out (drop=_name_);
by subject;
id test;
idlabel tstlabel;
var tstvalue;
run;
options nocenter;
proc print data=vital_out label;
run;
Height Weight
Obs subject (cm) (kg)
1 00-01 175 75
MayIn Windows, running a set of programs in batch mode is a common task. Below is an example of a batch file (filename is RUNALL_Tables.BAT) using two notable items:
An example of a batch fiile that I use is given below: rem Point to location where SAS.EXE is located set xsas=C:\Program Files\SAS\SAS 9.1 rem Run our programs "%xsas%\sas" Demographics.sas -nostatuswin -nologo "%xsas%\sas" AdverseEvents.sas -nostatuswin -nologo One advantage of this type of programming is that if the SAS executable gets moved it would be just a case of changing one line rather than several. JuneDespite procedures like CPORT and CIMPORT that put data into a way that it can be copied from one computer to another, the old Version 5 Transport file is still the most common way of transferring data between computers, apart for TAB delimited for CSV files (that for another day). One reason is that the Version 5 Transport file format is in the public domain while the format created and read by CPORT and CIMPORT is not. The limitations of the Version 5 Transport files are that they can only contain data (no view or catalogs for example), have variable names that are SAS version 5/6 compatible (starting with a character A-Z, no spaces, and be a maximum of 8 characters in length), and character variables of no more than 200 characters. Procedures CPORT and CIMPORT have no such limitations. The most common way to create a Version 5 Transport file is the use of the COPY procedure, as the following example demonstrates: libname derived_data 'C:\TAWI\RAWDATA'; libname xportout xport 'C:\TAWI\TRANSFER\TR002.XPT'; proc copy in=derived_data out=xportout memtype=data; run; In the example the datasets in the directory
referenced by the LIBNAME DERIVED_DATA are copied into
the transfer file TR002.XPT. It is also possible to use
the SELECT and EXCLUDE statements to restrict the
datasets that are processed. SAS documentation suggests
that it is important to use the "memtype=data"
option so that files selected for the transfer are only
those of dataset type. I personally suggest the use of
the XPT filename extension as this is the most way
Version 5 Transport files are named. To restore a Version 5 Transport file using the COPY procedure is done a similar way, as the following example demonstrates: libname xportin xport 'C:\TAWI\TRANSFER\TR002.XPT '; libname derived_data 'C:\TAWI\RAWDATA '; proc copy in=xportin out=derived_data; run; In the example all the datasets that are in the transport file TR002.XPT are restored in the directory referenced by the LIBNAME DERIVED_DATA, however this can be restricted if need be by the SELECT and EXCLUDE statements in the COPY procedure call. JulySometimes you need to set all variables of an observation to blank, as you do when you are creating a blank dataset (in the LOG you will see at message coming up that variables are initialized). The following example creates a dataset with zero observations and uses the CALL MISSING routine to avoid the “initialized variable” message appearing: 7 data PopulationData2008;
8 attrib year length=8 label='Year'
9 population length=8 format=comma10.
10 label='Population Count';
11 call missing(of _ALL_);
12 delete;
13 run;
NOTE: The data set WORK.POPULATIONDATA2008 has 0 observations and 2 variables. AugustTransferring a numeric variable to a character value is a common task, a lot more common than people realize – infact most people do not realize they are doing it. Take for example the following code that is inside a datastep: if hours>40 then
flagtxt='NOTE: Hours reported are greater than 40 – reported '||
'hours are: '||compress(put(hours,8.2.));
In the example we are placing the HOURS value that is stored as numeric to a character string. If we had just done: if hours>40 then
flagtxt='NOTE: Hours reported are greater than 40 – reported '||
'hours are: ‘||hours;
SAS would have brought up a message in the SAS LOG indicating that a numeric to character conversion had been done within SAS – these messages should be looked at. In the above example, the conversion is easy to spot, but not something like: Tothours = tothours + hours; where the variable HOURS is numeric and TOTHOURS is character – here some very funny things can go on and unexpected results occur. Let me show you now a classic example that I show people regarding this issue: 191 data _null_;
192 length x $8;
193 x = 1/3;
194 x = x * 3;
195 put x=;
196 run;
NOTE: Numeric values have been converted to character values
at the places given by: (Line):(Column).
193:9 194:10
NOTE: Character values have been converted to numeric values
at the places given by: (Line):(Column).
194:8
x=0.999999
The answer for x should of course be 1, not 0.999999 – what the program first did was calculate 1/3 and stored it as a character, then multiplied that value by 3. I did not copy across the LOG messages but it said that “NOTE: Numeric values have been converted to character values at the places given by …” To avoid the issues of numeric to character conversion, please always use a PUT statement with a format to put the correct numeric value into a character variable. Next month, Character to Numeric conversion. SeptemberContinuing on from last month, conversion of numeric to character, or character to numeric conversion is an issue. Lets take a look at the following example: 277 data TestDates;
278 infile cards;
279 input SubjectID $ 1-6 TestDate $ 8-15
280 @17 FirstTreatmentDate date9.;
281 put (_all_) (=);
282 cards;
SubjectID=000101 TestDate=06152008 FirstTreatmentDate=17198
SubjectID=000102 TestDate=08082008 FirstTreatmentDate=17332
NOTE: The data set WORK.TESTDATES has 2 observations and 3 variables.
285 ;
286 run;
287 data TestDatesModified;
288 attrib TestDateN length=8 format=date9.;
289 set TestDates;
290 TestDateN = TestDate; ** Convert TestDate to Numeric;
291 DaysSinceFirstTreatment = TestDateN - FirstTreatmentDate;
292 put (_all_) (=);
293 run;
NOTE: Character values have been converted to numeric
values at the places given by: (Line):(Column).
290:16
TestDateN=21AUG**** SubjectID=000101 TestDate=06152008 FirstTreatmentDate=17198
DaysSinceFirstTreatment=6134810
TestDateN=********* SubjectID=000102 TestDate=08082008 FirstTreatmentDate=17332
DaysSinceFirstTreatment=8064676
NOTE: There were 2 observations read from the data set WORK.TESTDATES.
NOTE: The data set WORK.TESTDATESMODIFIED has 2 observations and 5 variables.
As can be seen in the example, because we just blindly converted the TestDate variable from character to numeric, the values for TestDateN were strange. Note the log message above that gives an indication of the issue. To fix this we need to use and input statement with an informat, as the following example will show: 294 data TestDates; 295 infile cards; 296 input SubjectID $ 1-6 TestDate $ 8-15 297 @17 FirstTreatmentDate date9.; 298 put (_all_) (=); 299 cards; SubjectID=000101 TestDate=06152008 FirstTreatmentDate=17198 SubjectID=000102 TestDate=08082008 FirstTreatmentDate=17332 NOTE: The data set WORK.TESTDATES has 2 observations and 3 variables. 302 ; 303 run; 304 data TestDatesModified; 305 attrib TestDateN length=8 format=date9.; 306 set TestDates; 307 TestDateN = input(TestDate,mmddyy8.); ** Convert TestDate to Numeric; 308 DaysSinceFirstTreatment = TestDateN - FirstTreatmentDate; 309 put (_all_) (=); 310 run; TestDateN=15JUN2008 SubjectID=000101 TestDate=06152008 FirstTreatmentDate=17198 DaysSinceFirstTreatment=500 TestDateN=08AUG2008 SubjectID=000102 TestDate=08082008 FirstTreatmentDate=17332 DaysSinceFirstTreatment=420 NOTE: There were 2 observations read from the data set WORK.TESTDATES. NOTE: The data set WORK.TESTDATESMODIFIED has 2 observations and 5 variables. To avoid the issues of character to numeric conversion, please always use a INPUT statement with a informat to put the correct numeric value into a character variable. OctoberMaking a copy of a SAS dataset is a basic operation in SAS. This is usually done when we want to bring a dataset from a permanent area to a WORK area, or from a WORK area and put the dataset into a permanent area. One way of copying a SAS dataset from one directory to another, and to some is the most efficient, is to either use the COPY procedure, or the COPY statement in the DATASETS procedure. The syntax for the COPY procedure, when copying data, is: PROC COPY IN=source-library OUT=target-library MEMTYPE=DATA;
SELECT member-list;
EXCLUDE member-list;
RUN;
where
If the SELECT or EXCLUDE statements are not used then all datasets in the source directory will be copied to the target directory. If only select datasets are needed to be copied then the SELECT or EXCLUDE statements are useful - in the case of SELECT only those datasets that are listed will be copied, while in the case of EXCLUDE all datasets except those listed will be included. The following example selects the dataset CLASS from the SASHELP library and copies it to the WORK library, maybe for later manipulation: PROC COPY IN=sashelp OUT=work MEMTYPE=data;
SELECT class;
RUN;
The PROC DATASETS equivalent call for the above example is: PROC DATASETS LIBRARY=work;
COPY IN=sashelp OUT=work MEMTYPE=data;
SELECT class;
QUIT;
RUN;
NovemberA month ago I had a programmer ask me how could she set all the character variables in a SAS dataset to missing. This follows a tip I gave some time ago referring to creating a dataset with no observations, avoiding the message indicating that there were initialized variables. In SAS, the variables in a dataset are grouped by numeric (_NUMERIC_) and character (_CHAR_). Using this it is possible to set all the numeric or character variables to missing. The following code shows its use by setting the numeric variables to missing if the condition is met: 141 *** Load our test data; 142 data PregnancyTestResults; 143 infile cards; 144 input SubjectID $ 1-6 Sex $ 8 Tested Result; 145 ** TESTED and RESULT have codes 1=Yes, 0=No; 146 put (_all_) (=); 147 cards; SubjectID=000101 Sex=M Tested=1 Result=. SubjectID=000102 Sex=F Tested=1 Result=0 SubjectID=000103 Sex=F Tested=0 Result=. SubjectID=000104 Sex=M Tested=. Result=. SubjectID=000105 Sex=F Tested=1 Result=1 153 ; 154 run; NOTE: The data set WORK.PREGNANCYTESTRESULTS has 5 observations and 4 variables. 155 *** If Male then set TESTED and RESULT to missing (both numeric);
156 data PregnancyTestResultsModified;
157 set PregnancyTestResults;
158 if sex='M' then do;
159 put 'WAR' 'NING: Male subject cannot have ' /
160 @10 'a pregnancy test -- TESTED and RESULT ' /
161 @10 'will be set to blank: ' /
162 @10 (_all_) (=);
163 call missing(of _numeric_);
164 end;
165 run;
WARNING: Male subject cannot have
a pregnancy test -- TESTED and RESULT
will be set to blank:
SubjectID=000101 Sex=M Tested=1 Result=.
WARNING: Male subject cannot have
a pregnancy test -- TESTED and RESULT
will be set to blank:
SubjectID=000104 Sex=M Tested=. Result=.
NOTE: There were 5 observations read from the data set WORK.PREGNANCYTESTRESULTS.
NOTE: The data set WORK.PREGNANCYTESTRESULTSMODIFIED has 5 observations and 4 variables.
166 *** Put the dataset out to the SAS LOG, like a mini PROC PRINT;
167 data _null_;
168 set PregnancyTestResultsModified;
169 if _n_=1 then put 'Pregnancy Test Results' /;
170 put (_all_) (=);
171 run;
Pregnancy Test Results
SubjectID=000101 Sex=M Tested=. Result=.
SubjectID=000102 Sex=F Tested=1 Result=0
SubjectID=000103 Sex=F Tested=0 Result=.
SubjectID=000104 Sex=M Tested=. Result=.
SubjectID=000105 Sex=F Tested=1 Result=1
NOTE: There were 5 observations read from the data set WORK.PREGNANCYTESTRESULTSMODIFIED.
DecemberA couple of months ago I used the COPY and DATASETS procedure to copy a dataset from one library to another, but what if instead we wanted to make a copy of a dataset in the same directory, e.g. take the dataset CLASS (that was already copied from the SASHELP directory to the WORK directory) and make it CLASSCOPY (maybe we want to manipulate it without effecting the original dataset). You can certainly use a datastep as given in the following following example: DATA classcopy;
SET class;
RUN;
But another way is to use the AGE statement in the DATASETS procedure as given below: PROC DATASETS LIBRARY=work;
AGE class classcopy;
QUIT;
RUN;
Using the AGE statement will make a copy of the dataset CLASS and name it CLASSCOPY - if CLASSCOPY already exists then it will be overwritten by the data in CLASS. While this example is not a use that I would see very useful, it does have one other application. When creating a number of datasets that we put into a permanent area, it is useful to have a copy of the previous version for QC purposes, as the following example demonstrates: PROC DATASETS LIBRARY=derived_data;
AGE ae ae_prevversion;
COPY IN=WORK OUT=derived_data;
SELECT ae;
QUIT;
RUN;
In the example above, before the latest version of AE is put into the DERIVED_DATA library, a copy is made and named AE_PREVVERSION - this then can be used to compare against the latest version AE and see that changes expected actually occurred. |