| David Franklin, Blog | |
| 2011 Archive | |
| Back | dfranklinnh@gmail.com |
|
JanuaryIt is eight years since I started this so I am going to answer a question that I am asked often -- "What code do you use most often?" This month is the answer to that question. As a programmer who deals with a lot of data, I have a macro that produces a contents output for every dataset in a directory, along with a listing of the first 25 observations. This is a program that I have had in my "utility belt" for many years. The program (in the form a macro) is given below: %macro contprt(dir= /*Directory*/
,maxobs=25 /*Maximum number of OBS to print*/
);
** Define the LIBNAME of the data from DIR macro variable;
library mydata "&dir";
** Get a list of the datasets in the SAS file in the directory in question;
proc contents data=mydata._all_ out=_mycont (keep=memname memtype) noprint;
run;
** Create a unique list of the datasets, selecting only DATA types;
proc sort data=_mycont nodupkey;
by memname;
where memtype='DATA';
run;
** Create a list of the datasets, one macro variable for each. At the last
iteration put the number of datasets being produced into a macro variable.;
data _null_;
set _mycont end=eof;
call symput(compress('dsname'||put(_n_,8.)),compress(memname));
if eof then call symput('dsname0',put(_n_,8.));
run;
** Use a macro to produce CONTENTS and PRINT of each dataset, restricting
the number of observations to that defined in the MAXOBS macro variable.;
%do i=1 %to &dsname0;
proc contents data=mydata.&&dsname&i;
title "Contents of Dataset &&dsname&i";
run;
proc print data=mydata.&&dsname&i (obs=&maxobs);
title "First &maxobs of Dataset &&dsname&i";
run;
%end;
%mend contprt;
Now it must be remembered that I have had this macro since the late 1980's (SAS version 6.04), hence the odd way of placing each dataset into a seperate macro variable. SAS SQL would be some time off and character variables were restricted to 200 characters in length, but it works and has served me well. Using what is available today, can it be reworked? You bet. The section where the list of the datasets in a directory can be selected and made into macro variables can be rewritten to: proc sql noprint;
select distinct memname
into :dslist seperated by ' '
from sashelp.vmember
where memtype='DATA'
order by memname;
run;
Using this code we get a list of the datasets into a single macro variable, each dataset name seperated by a space. Because we have a list of the datasets in a single macro variable, the generation of the contents and print would change to the following: ** Use a macro to produce CONTENTS and PRINT of each dataset, restricting
the number of observations to that defined in the MAXOBS macro variable.;
%let i=1;
%do %while(%scan(&dslist,&i) ne );
proc contents data=mydata.%scan(&dslist,&i);
title "Contents of Dataset %scan(&dslist,&i)";
run;
proc print data=mydata.%scan(&dslist,&i) (obs=&maxobs);
title "First &maxobs of Dataset %scan(&dslist,&i)";
run;
%let i=%eval(i+1);
%end;
In this code the %SCAN function is used to select the name of the dataset, checked if it is non-missing (if missing we are at the end of the list) and then generate the CONTENTS and PRINT output. These two pieces of code is all that is needed in this version. There is yet another more code efficient method using a CALL EXECUTE within a single datastep, but that is for another day. Yes, SAS has advanced over the years, and yes there are many ways to do the same task, but I am sticking to my old macro -- it is tried and trusted. I hope this is useful. See you in February. FebruaryThis month I am going away from SAS briefly and into the world of VBA, but this tip is still very useful for SAS programmers. Typically when someone wants to print all MS Word or RTF files from a directory the common way is to select all the relevant files and select print which will print all pages of all documents selected. There are cases however where this is a waste of paper and we only need to print a few pages of each document. This can be easily carried out using the VBA code below: 01 Sub PrintFirstPages() 02 Dim strFileName As String 03 Dim strPath As String 04 strPath = "c:\temp\" 05 strFileName = Dir(strPath + "*.rtf", vbNormal) 06 Do While strFileName <> "" 07 Documents.Open FileName:=strPath + strFileName 08 Documents(strFileName).PrintOut Range:=wdPrintFromTo, From:="1", To:="2" 09 Documents.Close 10 strFileName = Dir 11 Loop 12 End Sub Line 1 defines the procedure, and lines 2 and 3 set the two internal variables. Line 4 specifies the directory path to use -- note, it must end with the '\'. Line 5 selects the files to print -- in this example it is the files with the extension .RTF but this is modifiable. Line 6 starts the loop where each file is opened (line 7) and the range of pages specified in line 8 is printed -- in this code pages 1 and 2 are printed, however if there is only one page in the document then that page is printed. The document is finally closed at line 9 and the next file in the selection is selected for print at line 10. To enter the VBA code into Microsoft Word select Tools > Macro > Visual Basic Editor then Insert > Module Then paste the code above and then press File > Close and Return to Microsoft Word The sequence above may vary due to the version of Microsoft Word that is on your system, but the basic steps are the same. The VBA macro is now ready for use within Word and can be accessed from the VBA macro library. I hope this is useful. See you in March. MarchWhen creating output in a text file, I like to have a horizontal line at the bottom followed by the program name and the date and time the program was run -- this latter part is usually right aligned. This would be easy if the linesize and pagesize were always the same, but different clients of mine want different settings based on their particular standard. For this task the pagesize is not important, but the linesize is. First, lets get the linesize and create a horizontal line: data _null_;
ls=getoption('LS'); *This will be the linesize of the output;
horizline=repeat('-',ls-1); *REPEAT function creates a text string;
call symput("horizline",strip(horizline)); *Put line as a macro variable;
run;
footnote1 "&horizline";
The FOOTNOTE1 statement will now produce a single horizontal line of '-'s for the entire width of the output. The next piece is to get the name of the program being run AND put the date and time the program is run in the second line of footnotes. First, lets get the name of the program: data _null_;
length pgmfull $200 pgm $100;
pgmfull=getoption('sysin'); *Program Name and Directory;
pgm=scan(pgmfull,-1,'\'); *Program Name only;
if missing(pgm) then
pgm='INTERACTIVE'; *If Interactive, name and directory not present when run in BATCH mode;
run;
Next is the date and time of run; data _null_;
dtrun="Date: &sysdate9., &systime.";
run;
Now lets bring these two pieces of information together: data _null_;
length pgmfull $200 pgm $100 dtrun $50;
ls=getoption('ls'); *Need this for linesize;
pgmfull=getoption('sysin'); *Program Name and Directory;
pgm='Program: '||scan(pgmfull,-1,'\'); *Program Name only;
if missing(pgmfull) then
pgm='Program: INTERACTIVE';
*If Interactive, name and directory not present -- only in BATCH mode;
dtrun="Date: &sysdate9., &systime.";
foot2txt=repeat(' ',ls-1); *Create initial blank line;
substr(foot2txt,1,length(pgm))=strip(pgm);
*Replace the first characters of the string with the program name;
substr(foot2txt,ls-length(dtrun)+1)=strip(dtrun);
*Replace the last characters of the string with date and time of run;
call symput('foot2txt',strip(foot2txt));
run;
footnote2 "&foot2txt";
Rather than have two separate datasteps to create our two footnotes, lets create one: data _null_;
ls=getoption('LS'); *This will be the linesize of the output;
horizline=repeat('-',ls-1); *REPEAT function creates a text string;
call symput("horizline",strip(horizline)); *Put line as a macro variable;
length pgmfull $200 pgm $100 dtrun $50;
pgmfull=getoption('sysin'); *Program Name and Directory;
pgm='Program: '||scan(pgmfull,-1,'\'); *Program Name only;
if missing(pgmfull) then
pgm='Program: INTERACTIVE';
*If Interactive, name and directory not present -- only in BATCH mode;
dtrun="Date: &sysdate9., &systime.";
foot2txt=repeat(' ',ls-1); *Create initial blank line;
substr(foot2txt,1,length(pgm))=strip(pgm);
*Replace the first characters of the string with the program name;
substr(foot2txt,ls-length(dtrun)+1)=strip(dtrun);
*Replace the last characters of the string with date and time of run;
call symput('foot2txt',strip(foot2txt));
run;
Now, lets see this in action: ========= PROGRAM =========
*--------------------------------------------------*;
* Setup;
*--------------------------------------------------*;
options ls=68 ps=30 nonumber nodate;
run;
*--------------------------------------------------*;
* Our data;
*--------------------------------------------------*;
data USMountains;
infile cards;
input name $ 1-28 state $29-36 height 37-41;
*...x....1....x....2....x....3....x....4.; * Just a ruler to help in column spec.;
cards;
Mount McKinley Alaska 20320
Mount McKinley, North Peak Alaska 19470
Mount Saint Elias Alaska 18008
Mount Foraker Alaska 17400
Mount Bona Alaska 16500
Mount Blackburn Alaska 16390
Mount Sanford Alaska 16237
South Buttress Alaska 15885
Mount Vancouver Alaska 15700
Mount Churchill Alaska 15638
;
run;
*--------------------------------------------------*;
* Setup the horizontal line and text with program;
* name and date/time of run;
*--------------------------------------------------*;
data _null_;
ls=getoption('LS'); *This will be the linesize of the output;
horizline=repeat('-',ls-1); *REPEAT function creates a text string;
call symput("horizline",strip(horizline)); *Put line as a macro variable;
length pgmfull $200 pgm $100 dtrun $50;
pgmfull=getoption('sysin'); *Program Name and Directory;
pgm='Program: '||scan(pgmfull,-1,'\'); *Program Name only;
if missing(pgmfull) then
pgm='Program: INTERACTIVE';
*If Interactive, name and directory not present -- only in BATCH mode;
dtrun="Date: &sysdate9., &systime.";
foot2txt=repeat(' ',ls-1); *Create initial blank line;
substr(foot2txt,1,length(pgm))=strip(pgm);
*Replace the first characters of the string with the program name;
substr(foot2txt,ls-length(dtrun)+1)=strip(dtrun);
*Replace the last characters of the string with date and time of run;
call symput('foot2txt',strip(foot2txt));
run;
*--------------------------------------------------*;
* Output;
*--------------------------------------------------*;
proc print data=USMountains noobs label;
title1 "Listing of the Ten Highest Mountains in the US";
title2 "Reference: http://www.americasroof.com/fourteeners.shtml, 03MAR2011";
footnote1 "&horizline";
footnote2 "&foot2txt";
var name state height;
label name='Name'
state='State'
height='Height (feet)';
format height comma8.;
run;
========= OUTPUT =========
Listing of the Ten Highest Mountains in the US
Reference: http://www.americasroof.com/fourteeners.shtml, 01MAR2011
Height
Name State (feet)
Mount McKinley Alaska 20,320
Mount McKinley, North Peak Alaska 19,470
Mount Saint Elias Alaska 18,008
Mount Foraker Alaska 17,400
Mount Bona Alaska 16,500
Mount Blackburn Alaska 16,390
Mount Sanford Alaska 16,237
South Buttress Alaska 15,885
Mount Vancouver Alaska 15,700
Mount Churchill Alaska 15,638
--------------------------------------------------------------------
Program: USHighestMountains Date: 03MAR2011, 09:17
In nearly all the code I present on this site, the SAS Code and Output is presented for you to copy and play with -- I encourage you to change some of the code and see what it happens. I hope this is useful. See you in April. AprilA very short tip this month. To include the SAS source statements included with an %INCLUDE statement in a SAS LOG, the system option SOURCE2 should be used. By default the system option setting is NOSOURCE2 that results in the source statements not being printed in the SAS LOG. MayThis month I am going away from SAS briefly, as I did in February, and into the world of VBA, but this tip is still very useful for SAS programmers. I recently had the situation where a number of RTF files had to be converted into Text files. Rather than open each file and save it as a text, it was necessary to find a way to do this via a program for every file in a specified directory. It could have been done in SAS, but a VBA macro in Word was a better solution. The following is the code that made up the VBA macro: Sub Rtf2Txt()
Dim strFileName As String
Dim strOutFileName As String
Dim strPath As String
'Select the directory where RTF files are located
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = "C:\"
.Show
strPath = .SelectedItems(1)
End With
'All *.RTF files in the directory selected
strFileName = Dir(strPath + "\*.rtf", vbNormal)
Do While strFileName <> ""
'Create an output file name from original name
strOutFileName = Replace(strFileName, ".rtf", ".txt")
strOutFileName = Replace(strOutFileName, ".RTF", ".txt")
'Open RTF File and save as a TXT file
Documents.Open FileName:=strPath + "\" + strFileName, _
ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _
wdOpenFormatAuto, XMLTransform:=""
ActiveDocument.SaveAs FileName:=strPath + "\" + strOutFileName, _
FileFormat:=wdFormatText, LockComments:=False, Password:="", _
AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
:=False, SaveAsAOCELetter:=False, Encoding:=1252, InsertLineBreaks:=True _
, AllowSubstitutions:=False, LineEnding:=wdCRLF
Documents.Close
' Get the next file in the directory
strFileName = Dir
Loop
End Sub
To enter the VBA code into Microsoft Word select Tools > Macro > Visual Basic Editor then Insert > Module Then paste the code above and then press File > Close and Return to Microsoft Word The sequence above may vary due to the version of Microsoft Word that is on your system, but the basic steps are the same. The VBA macro is now ready for use within Word and can be accessed from the VBA macro library. Note that this VBA code was tested on Word 2003 and 2007, does not have error capture code, and is given "as is" without any implied warranty or support. I hope this is useful. If you are going to PharmaSUG this year, I will be there -- say 'HI!'. See you in June. JuneSometimes when a dataset is printed no observations are present. By default, SAS will not print the dataset but on occasions the requirement is to print out a message with the fact that there are no observations. First, lets but together a macro that reports the number of observations in a dataset: %macro numobs(dsn);
%global num;
data _null_;
if 0 then set &dsn nobs=nobs;
call symput('num',trim(left(put(nobs,8.))));
stop;
run;
%mend numobs;
The macro opens the dataset specified in the DSN parameter and put the number of records found in a global macro variable called NUM. Now for some data -- this is a set of fictitious test scores for a class: data _testscores;
attrib name length=$10
score length=8;
infile cards;
input name $ score;
cards;
Jacob 54
Isabella 95
Ethan 99
Sophia 68
Michael 75
Emma 75
Jayden 65
Olivia 74
William 56
Ava 71
;
run;
For the purposes of this example, our program has to report the people who scored less than 50 in the test -- we can see in the data that there were none we have to generate the report saying that. Lets write the code to subset the data, finding those records where the score is less than 50: data _lt50a;
set _testscores;
where score<50;
run;
Now lets call the macro to count the number of records: %numobs(_lt50a); With the number of records known to print, in this case zero, it is now time to create a dummy record with the message that "No observations meet the criteria": %macro test;
%if &num=0 %then %do; **Check if zero records found.;
data _lt50a;
length name $50; **Change to allow for no observation message;
set _lt50a;
data _noobs;
length name $50; **Change to allow for no observation message;
set _testscores (obs=1); **Take first observation only;
call missing(of _all_); **Set all variables in the dataset to missing;
name='No observations meet criteria'; **Set message in NAME variable;
proc append base=_lt50a data=_noobs force; **Append to dataset that is being printed;
run;
%end;
%mend test;
%test;
First thing to note here is that this is done within a macro so that we can first conditionally test to see if the number of observations are zero, and if so, process further. The dataset _LT50a is read first, amending the size of the variable NAME to 50 characters to store the no observations message -- this is needed before we make the message. The dataset _NOOBS is generated from the ORIGINAL dataset as this assumes that there is one or more records in it, not from the _LT50A dataset as this we know has no records -- also note, only the first record is needed. The CALL MISSING statement sets all the variables in the record to missing, then in the next line we put the message to print in the listing if no observations are found. In the last step of the macro, the PROC APPEND procedure is called and the record appended. Finally, the PROC PRINT procedure to print the report: proc print data=_lt50a noobs label;
var name score;
title1 "Listing of Students With Score Less Than 50";
run;
If there are records found with a score of less tan 50 then the code in the macro TEST would not run as the macro variable NUM would not equal zero. Hope this is useful. As always, try this code, play with it, and see what it does. JulyIf you run a program to produce a report on a regular basis it is sometimes useful to automate the delivery of the report in an email using SAS. Below is an example where a program is run that produces a report and then sends the report that is generated as an email to those in the To:, CC: and BCC: fields: *SAS Code to produce the report;
*Close RTF file destination;
ods rtf close;
*Send report in email;
filename outmsg email;
data _null_;
file outmsg
to="TheBigBoss@company.com"
cc=("DepartmentAManager@company.com" "DepartmentBManager@company.com")
bcc="MySelf@company.com"
subject="Monthly Timesheet Report for June 2011"
attach = "c:\Timesheet\MonthlyReport_201106Jun.rtf";
put 'Dear All';
put;
put 'Attached is the Monthly Timesheet Report for June 2011.';
put;
put 'MySelf and I,';
put 'Timesheet Administrator';
run;
In the example the email is sent to the addresses in the To:, CC: and BCC: fields (make a copy to myself to make sure that the email was sent and keep a copy of the email for reference purposes) with the subject "Monthly Timesheet Report for June 2011", attaching the report using the attach= option. It is possible to add some conditional code before the FILENAME statement so that if an ERROR is detected prior to the emailing step, the email is not sent, but this code is for another day. The example shows a "hardcoded" style but the program will in reality have the month and year values in the Subject and text of the message, and the filename will also be generated from these values. Hope this is useful. See you in August. AugustYou have created a list of values in a macro variable separated by a space, but question is, how many values do I have? Lets look at the following example: *Lets make our data;
data _idnum0;
infile cards;
input idnum $4;
cards;
0001
0002
0003
0004
0005
0006
;
run;
*Create a macro variable of the unique IDs separated by a space, using SQL;
proc sql noprint;
select distinct idnum
into :idlist separated by ' '
from _idnum0;
quit;
run;
*Count the number of IDs in the list;
%let _count=%sysfunc(countw(&idlist));
*Put out number of IDs in the SAS LOG;
%put There are &_count IDs in the macro variable IDLIST;
run;
When the code is run the following message will appear in the SAS LOG: There are 6 IDs in the macro variable IDLIST Which is the number of distinct IDs in the data. The question you may be asking is why use the DISTINCT keyword in the SQL? It is true that in this case the IDs are unique so the DISTINCT keyword is not needed, but if the list was not unique and you were wanting to do some BY processing, the DISTINCT will create that unique list, as the following example will show: *Our data;
data _idcomments0;
attrib idnum length=$4
date length=$10 label='Date'
comment length=$50 label='Comment';
infile cards;
input idnum $ date $ comment & $;
cards;
0001 2011-01-15 Payment late due to hospitalization.
0001 2011-01-19 Came in to discuss late payment.
0002 2011-01-14 Made note to review complete file in February.
0003 2011-01-17 Made visit as contents of property was destroyed thru flood damage.
;
run;
*Make unique list of subjects;
proc sql noprint;
select distinct idnum
into :idlist separated by ' '
from _idcomments0;
quit;
run;
*Count the number of IDs in the list and put number out to SAS LOG;
%let _count=%sysfunc(countw(&idlist));
%put There are &_count IDs in the macro variable IDLIST;
run;
*Make sure dataset _IDCOMMENTS0 is in ID/DATE order;
proc sort data=_idcomments0;
by idnum date;
run;
*Use a macro to print the comments;
%macro tmp;
title1 "We Always Pay Up Insurance";
title2 "Listing of Comments";
%let i=1; *Internal counter;
%do %while(%scan(&idlist,&i," ") ne ); *DO loop while IDs exist in macro variable;
title3 "Customer ID: %scan(&idlist,&i)"; *Set title as ID;
proc print data=_idcomments0 label noobs; *PROC PRINT call;
var date comment;
where idnum="%scan(&idlist,&i)"; *Subset data by ID;
run;
%let i=%eval(&i+1); *Increment internal counter;
%end;
%mend tmp;
%tmp;
run;
In the example, the number of distinct IDNUM values is put in a macro variable and a note of the number of unique IDs put out into the SAS LOG -- this is the expected number of outputs we are expecting. In the macro we are going through each IDNUM value in the list and using PROC PRINT to output the comments for that ID. As always, take this code and try it on your version of SAS (a word of warning, the SYSFUNC call will only work on SAS versions 9.1 and above or WPS versions 2.5 and above -- if you have another version, ask me). This could be better done with a PROC PRINT and a BY statement, or even using the CALL EXECUTE call, but the use of the macro this way shows a way that you could use a macro loop in more complex situations, for example a PROC REPORT with a PROC GCHART call by each center, on the one page. Hope this is useful. See you in September. SeptemberA short, but useful, tip this month. When SAS starts, either interactively or in batch, several files are accessed including the CONFIG and AUTOEXEC files. But how do you list what is run during initialization to your SAS LOG? The answer is the VERBOSE option, however the default is NOVERBOSE, so you have to manually set the setting. This option can diagnose SAS initialization issues or spot SAS options being changed from default values. Hope this is useful. See you in October. OctoberSometimes we want a SAS statistical procedure to capture the results as a SAS dataset. Often we can use an OUT option, or similar, that many statistical procedures use but occasionally we need more statistics produced that are not available using this format. Welcome the ODS OUTPUT feature in SAS version 9.1 and WPS version 2.4 and beyond. Before I go too far, lets load some data where we are going to capture the Quantiles using the PROC UNVARIATE procedure: data River_US (label='Rivers in US over 1000 miles in Length');
attrib name length=$40 label='River Name'
distance length=8 label='Distance (miles)';
input Name $ & distance @@;
cards;
Mississippi 2340
Columbia 1240
Colorado 1450
St. Lawrence 1900
Missouri 2540
Ohio 1310
Rio Grande 1900
Brazos 1280
Snake 1040
Atchafalaya 1420
Yukon 1980
Red 1290
Arkansas 1460
;
run;
Almost all the statistical procedures produce internal outputs that can be captured by ODS, it is just a case of finding out which internal output to capture. To find these internal outputs use the "ODS TRACE sandwich" as seen below: ODS TRACE ON;
proc univariate data=River_US;
var distance;
run;
ODS TRACE OFF;
Running the code above we get the following information in the SAS LOG: Output Added: ------------- Name: Moments Label: Moments Template: base.univariate.Moments Path: Univariate.distance.Moments ------------- Output Added: ------------- Name: BasicMeasures Label: Basic Measures of Location and Variability Template: base.univariate.Measures Path: Univariate.distance.BasicMeasures ------------- Output Added: ------------- Name: TestsForLocation Label: Tests For Location Template: base.univariate.Location Path: Univariate.distance.TestsForLocation ------------- Output Added: ------------- Name: Quantiles Label: Quantiles Template: base.univariate.Quantiles Path: Univariate.distance.Quantiles ------------- Output Added: ------------- Name: ExtremeObs Label: Extreme Observations Template: base.univariate.ExtObs Path: Univariate.distance.ExtremeObs ------------- Output Added: ------------- Name: MissingValues Label: Missing Values Template: base.univariate.Missings Path: Univariate.distance.MissingValues ------------- with the following output: The UNIVARIATE Procedure
Variable: distance (Distance (miles))
Moments
N 8 Sum Weights 8
Mean 1745 Sum Observations 13960
Std Deviation 504.494089 Variance 254514.286
Skewness 0.56850936 Kurtosis -1.2949709
Uncorrected SS 26141800 Corrected SS 1781600
Coeff Variation 28.910836 Std Error Mean 178.365596
Basic Statistical Measures
Location Variability
Mean 1745.000 Std Deviation 504.49409
Median 1675.000 Variance 254514
Mode 1900.000 Range 1300
Interquartile Range 825.00000
Tests for Location: Mu0=0
Test -Statistic- -----p Value------
Student's t t 9.783277 Pr > |t| <.0001
Sign M 4 Pr >= |M| 0.0078
Signed Rank S 18 Pr >= |S| 0.0078
Quantiles (Definition 5)
Quantile Estimate
100% Max 2540
99% 2540
95% 2540
90% 2540
75% Q3 2120
50% Median 1675
25% Q1 1295
10% 1240
5% 1240
1% 1240
0% Min 1240
The UNIVARIATE Procedure
Variable: distance (Distance (miles))
Extreme Observations
----Lowest---- ----Highest---
Value Obs Value Obs
1240 2 1450 3
1280 8 1900 4
1310 6 1900 7
1450 3 2340 1
1900 7 2540 5
Missing Values
-----Percent Of-----
Missing Missing
Value Count All Obs Obs
. 4 33.33 100.00
If we look at the output in the OUTPUT window you will notice that the section labels refer to the internal output sections in the LOG file. For this example I want to collect the first and third quartile which match to the labels '25% Q1' and '75% Q3'. The ODS TRACE "sandwich" code above is replaced with an ODS OUTPUT "sandwich" with the section name, in our case "Quantiles" and the output dataset name we want to use -- see below for our example: ODS OUTPUT Quantiles=_Q0;
proc univariate data=River_US;
var distance;
run;
ODS OUTPUT CLOSE;
Using a PROC PRINT to look at the output from dataset _Q0 we get the following: proc print data=_Q0 width=minimum;
run;
Obs VarName Quantile Estimate
1 distance 100% Max 2540
2 distance 99% 2540
3 distance 95% 2540
4 distance 90% 2540
5 distance 75% Q3 2120
6 distance 50% Median 1675
7 distance 25% Q1 1295
8 distance 10% 1240
9 distance 5% 1240
10 distance 1% 1240
11 distance 0% Min 1240
The actual dataset, _Q0, contains more than the first and third quartile but this information is now ready to be used elsewhere. I hope this is useful. See you in November. NovemberLast month I showed how to capture results of a SAS procedure to a dataset using ODS -- this month I am capturing results from a SAS procedure again but this time doing it a way that was pre-ODS, that is before ODS came into existance. It was mentioned that many SAS procedures have ways of getting the dataset using an OUT option or similar that many procedures have. Lets see this in action when I capture the Quantiles from PROC UNIVARIATE using the same data: data River_US (label='Rivers in US over 1000 miles in Length');
attrib name length=$40 label='River Name'
distance length=8 label='Distance (miles)';
input Name $ & distance @@;
cards;
Mississippi 2340
Columbia 1240
Colorado 1450
St. Lawrence 1900
Missouri 2540
Ohio 1310
Rio Grande 1900
Brazos 1280
Snake 1040
Atchafalaya 1420
Yukon 1980
Red 1290
Arkansas 1460
;
run;
proc univariate data=River_US;
var distance;
output out=_Q0
MEDIAN=MEDIAN P1=P1 P5=P5 P10=P10 P90=P90 P95=P95
P99=P99 Q1=Q1 Q3=Q3 MIN=MIN MAX=MAX;
proc print data=_Q0;
title1 "Listing of Quantiles Output from Univariate Procedure";
run;
=== OUTPUT ===
Listing of Quantiles Output from Univariate Procedure
Obs MAX P99 P95 P90 Q3 MEDIAN Q1 P10 P5 P1 MIN
1 2540 2540 2540 2540 2120 1675 1295 1240 1240 1240 1240
This method is very good. In most cases SAS Procedures that calculate statistics will capture all the output in a SAS dataset, however there are some output where this is not available in a SAS dataset, and may not be captured though ODS. An example of this is in the PROC UNIVARIATE output, Extreme Observations section. So how do we capture the information that a SAS procedure puts out on a listing but is not avaiable in a SAS dataset form? An old trick that used to be used was to output the listing to a text file using PROC PRINTTO and then reading that file back in capturing the information we wanted and saving that as a SAS dataset. The following example shows how to capture the Quantiles and Extreme Observations output from PROC UNIVARIATE using this technique: *Capture the output using PROC PRINTTO;
proc printto file='c:\temp\UnivariateOutput.txt' new;
proc univariate data=River_US;
var distance;
proc printto;
run;
*Load in the file produced by PROC PRINTTO and capture required results;
data _Q1; *Quantiles from PROC UNIVARIATE using PROC PRINTTO;
retain QuantileFlg 0; *Indication if in Quartiles section;
attrib VarName length=$8
Quantile length=$10
Estimate length=8 format=5.;
infile 'c:\temp\UnivariateOutput.txt' length=len;
input _txt $varying256. len;
keep VarName Quantile Estimate;
*Capture results;
if QuantileFlg=2 then do; *In Quantiles Section with results;
VarName='distance';
if lengthn(strip(_txt))>0 then do;
Quantile=substr(strip(_txt),1,10);
Estimate=input(substr(strip(_txt),11),best.);
output;
end;
end;
*Flag which section we are in;
if strip(_txt)=:'Quantiles'
then QuantileFlg=1; *In Section;
else if strip(_txt)=:'Quantile' and QuantileFlg=1
then QuantileFlg=2; *In results;
else if strip(_txt)=:'0% Min'
then QuantileFlg=0; *End of Section, no more results;
run;
data _E0; *Extremes from PROC UNIVARIATE using PROC PRINTTO;
retain ExtremeFlg 0; *Indication if in Extremes section;
attrib LowValue length=8
LowFreq length=8
HighValue length=8
HighFreq length=8;
infile 'c:\temp\UnivariateOutput.txt' length=len;
input _txt $varying256. len;
keep LowValue LowFreq HighValue HighFreq;
if strip(_txt)=:'Missing Values' and ExtremeFlg=2
then ExtremeFlg=0; *End of Section, no more results;
if ExtremeFlg=2 then do; *In Extremes Section with results;
if lengthn(strip(_txt))>0 then do;
LowValue=input(scan(strip(_txt),1),best.);
LowFreq=input(scan(strip(_txt),2),best.);
HighValue=input(scan(strip(_txt),3),best.);
HighFreq=input(scan(strip(_txt),4),best.);
output;
end;
end;
if strip(_txt)=:'Extreme Observations'
then ExtremeFlg=1; *In Section;
else if strip(_txt)=:'Value' and ExtremeFlg=1
then ExtremeFlg=2; *In results;
run;
proc print data=_Q1;
title1 "Listing of Quantiles Output using PROC PRINTTO from Univariate Procedure";
run;
proc print data=_E0;
title1 "Listing of Extremes Output using PROC PRINTTO from Univariate Procedure";
run;
=== OUTPUT ===
Listing of Quantiles Output using PROC PRINTTO from Univariate Procedure
Obs VarName Quantile Estimate
1 distance 100% Max 2540
2 distance 99% 2540
3 distance 95% 2540
4 distance 90% 2540
5 distance 75% Q3 2120
6 distance 50% Median 1675
7 distance 25% Q1 1295
8 distance 10% 1240
9 distance 5% 1240
10 distance 1% 1240
11 distance 0% Min 1240
Listing of Extremes Output using PROC PRINTTO from Univariate Procedure
Low Low High High
Obs Value Freq Value Freq
1 1240 2 1450 3
2 1280 8 1900 4
3 1310 6 1900 7
4 1450 3 2340 1
5 1900 7 2540 5
Note that it is necessary to have a knowledge of the output text file structure when bringing in the results of a SAS procedure -- the output created here uses the default output format. Capturing the results using the PROC PRINTTO technique is more work than using ODS or OUT options from the SAS procedures that create statistcs, but is still useful to know. Copy the code and try it on your SAS or WPS installation -- change a few things and see what happens. I hope this is useful. See you in December. DecemberI received an email from Rebecca in Boston asking how some values were being overwritten when two datasets were being joined. A few minutes of discussion and the problem was solved -- a common variable was in both datasets but not in the BY statement. Lets look at an example: data _sales (label='Sales Hours');
label client='Client'
value='Invoiced Amount'
number='Number of Hours';
infile cards;
input client $ value number;
cards;
Huey 1200 120
Dewey 1500 110
Louie 800 5
;
run;
data _budgeted (label='Budgeted Hours');
label client='Client'
number='Budgeted Hours'
manager='Manager';
infile cards;
input client $ number manager $;
cards;
Huey 120 Mickey
Dewey 80 Donald
Louie 50 Goofy
;
run;
Here we have two datasets which we are going to merge by client -- it is easy to see in this example that there are two common variables, CLIENT and NUMBER, the former which we are using to merge the data by. Now lets look at what happens after we merge the two datasets: proc sort data=_sales;
by client;
proc sort data=_budgeted;
by client;
data _merged;
merge _sales _budgeted;
by client;
proc print data=_merged;
title1 "Merged Sales and Budgeted Numbers";
run;
*=== Output ===;
Merged Sales and Budgeted Numbers
Invoiced Number
Obs Client Amount of Hours Manager
1 Dewey 1500 80 Donald
2 Huey 1200 120 Mickey
3 Louie 800 50 Goofy
*=== LOG ===;
106 data _merged;
107 merge _sales _budgeted;
108 by client;
NOTE: There were 3 observations read from the data set
WORK._SALES.
NOTE: There were 3 observations read from the data set
WORK._BUDGETED.
NOTE: The data set WORK._MERGED has 3 observations and 3
variables.
109 proc print data=_merged;
110 title1 "Merged Sales and Budgeted Numbers";
111 run;
NOTE: There were 3 observations read from the data set
WORK._MERGED.
112 proc print data=_merged label;
113 title1 "Merged Sales and Budgeted Numbers";
114 run;
NOTE: There were 3 observations read from the data set
WORK._MERGED.
Looking at the LOG and the OUTPUT everything looks reasonable, particularly if you are unfamiliar with what the expected output is. But look at the Number of Hours which the data, according to the label, comes from the _SALES dataset -- these are not the values from _SALES, but the values from _BUDGETED! No one would detect the issue and the report could go out to the public. Preventing the issue looks easy, just make sure each dataset has unique variable names except those that are being used on the MERGE statement. But it is not so easy if you are merging two datasets where one or both have several hundred variables. So what can we do to detect this issue in our programs? SAS/WPS does provide an option called MSGLEVEL that controls the level of detail in messages that are written to the SAS LOG. This has two values: N - prints notes, warnings, and error messages (default)
I - prints additional notes that refer to index usage, merge processing,
sort utilities, and those using option N
Lets use the MSGLEVEL option now with a value of I and see what comes up in the SAS LOG: *=== Output ===;
Merged Sales and Budgeted Numbers
Invoiced Number
Obs Client Amount of Hours Manager
1 Dewey 1500 80 Donald
2 Huey 1200 120 Mickey
3 Louie 800 50 Goofy
*=== LOG ===;
115 options msglevel=I;
116 proc sort data=_sales;
117 by client;
NOTE: Input data set is already sorted, no sorting done.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.03 seconds
cpu time 0.00 seconds
118 proc sort data=_budgeted;
119 by client;
NOTE: Input data set is already sorted, no sorting done.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
120 data _merged;
121 merge _sales _budgeted;
122 by client;
INFO: The variable number on data set WORK._SALES will be
overwritten by data set WORK._BUDGETED.
NOTE: There were 3 observations read from the data set
WORK._SALES.
NOTE: There were 3 observations read from the data set
WORK._BUDGETED.
NOTE: The data set WORK._MERGED has 3 observations and 3
variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
123 proc print data=_merged label;
124 title1 "Merged Sales and Budgeted Numbers";
125 run;
NOTE: There were 3 observations read from the data set
WORK._MERGED.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
The output is the same, but an INFO message now appears in the SAS LOG with the following text: INFO: The variable number on data set WORK._SALES will be
overwritten by data set WORK._BUDGETED.
Please note that the message may not be exactly the same depending on whether what version of SAS or WPS you are using, but the meaning is the same. This is our only indication in the SAS LOG, without knowing the data or what to expect, that numbers are being overwritten from _BUDGETED to _SALES when the merge occurs -- this is not an ERROR or WARNING message in the SAS LOG. With this information we can amend our programming accordingly so the report is correct either by dropping the NUMBER variable in _SALES or _BUDGETED, or renaming one of the two NUMBER variables and adding an additional column: Merged Sales and Budgeted Numbers
(with NUMBER dropped from _BUDGETED dataset)
Invoiced Number
Obs Client Amount of Hours Manager
1 Dewey 1500 110 Donald
2 Huey 1200 120 Mickey
3 Louie 800 5 Goofy
Merged Sales and Budgeted Numbers
(with NUMBER in _BUDGETED renamed to another variable name during MERGE)
Invoiced Number Budgeted
Obs Client Amount of Hours Hours Manager
1 Dewey 1500 110 80 Donald
2 Huey 1200 120 120 Mickey
3 Louie 800 5 50 Goofy
When you are writing your program, it is useful to use the MSGLEVEL option with the value of I, and always do little checks of the output at each step to make sure that what is being output is what you are expecting! Have a safe than happy holiday season. See you all next year. |