| David Franklin, Blog | |
| 2012 Archive | |
| Back | dfranklinnh@gmail.com |
|
January"I have a dataset that I want to break apart by region, and I want the dataset created to have the name of that region -- how do I do it?" That was a question someone asked me at PharmaSUG last year. A very interesting question. Lets look at some data first: data _AllPets;
infile cards;
input pet $ 1-3 name $4-12;
cards;
Cat Pacha
Cat Muschi
Dog Mia
Dog Dixie
Dog Princess
Cat Archie
Dog Bella
Cat Princess
Cat Boots
Dog Gus
Cat Milly
Dog Bailey
Cat Max
Dog Roxie
Dog Cody
Dog Boomer
Cat Sooty
Cat Shadow
Dog Tucker
Cat Fraidy
;
run;
In this example we have a dataset with a name (variable NAME) and whether they are are Cat or a Dog -- what we want to do is take this dataset and create two datasets, one called CAT and the other DOG. It is easy to see from the data that this could be done in one datastep as shown below: data cat dog;
set _AllPets;
if pet='Cat' then output cat;
else if pet='Dog' then output dog;
run;
But what if this was a very large dataset containing a hundred PET values with a thousand NAME values -- not so easy. So lets look at something a little more general. Lets first create a macro variable with the distinct PET values: proc sql noprint;
select distinct pet
into :petval separated by " "
from _AllPets;
quit;
run;
Running this code creates a macro variable with the distinct PET values, separated by a space. Now we are going to create a macro that will use each value of PET in this macro variable PETVAL and create a dataset with the same name: %macro breakup;
*Initialize macro counter;
%let i=1;
*A DO loop to actually do the breakup -- read each value
of PET contained in the macro variable PETVAL until no more;
%do %while(%scan(&petval,&i) ne );
*For each value of PET create a dataset with that VALUE,
dropping PET from the outgoing dataset;
data %scan(&petval,&i);
set _AllPets;
where pet="%scan(&petval,&i)";
drop pet;
run;
*Increment the DO LOOP counter;
%let i=%eval(&i+1);
%end;
%mend breakup;
Running this macro will read each value from the macro variable PETVAL, create a dataset with that value, and use a WHERE statement to do the selection of the records from _ALLPETS. Using a PRINT procedure call on each the two datasets created, the following data is present: List of Cat Names
Obs name
1 Pacha
2 Muschi
3 Archie
4 Princess
5 Boots
6 Milly
7 Max
8 Sooty
9 Shadow
10 Fraidy
List of Dog Names
Obs name
1 Mia
2 Dixie
3 Princess
4 Bella
5 Gus
6 Bailey
7 Roxie
8 Cody
9 Boomer
10 Tucker
Take this code and try it out and see what it does. Remember to have the MPRINT, MLOGIC and SYMBOLGEN options on. See you in Feburary. FebruaryLast month I showed how to break apart a dataset into seperate datasets using one of the values as a dataset name -- this month I am going to make it a little more general where you just have to use a macro, enter a dataset name and a variable. Lets first slip the PROC SQL call into the macro BREAKUP: *A macro to actually do the subset;
%macro breakup;
*Get a unique list of PET values;
proc sql noprint;
select distinct pet
into :petval separated by " "
from _AllPets;
quit;
run;
*Initialize macro counter;
%let i=1;
*A DO loop to actually do the breakup -- read each value
of PET contained in the macro variable PETVAL until no more;
%do %while(%scan(&petval,&i) ne );
*For each value of PET create a dataset with that VALUE,
dropping PET from the outgoing dataset;
data %scan(&petval,&i);
set _AllPets;
where pet="%scan(&petval,&i)";
drop pet;
run;
*Increment the DO LOOP counter;
%let i=%eval(&i+1);
%end;
%mend breakup;
It we run the macro with the same data then you will see the same results as last time. I may not look it, but we are actually just about there with a general macro that will break apart a dataset based on the values of a variable -- all we need to do is change all dataset references to a macro variable, and the same for variable references to a macro variable, as shown below: *A macro to actually do the subset;
%macro breakup(dsn= /*Dataset name*/
,dsv= /*Variable in dataset to use split by value*/
);
*Get a unique list of values;
proc sql noprint;
select distinct &dsv
into :petval separated by " "
from &dsn;
quit;
run;
*Initialize macro counter;
%let i=1;
*A DO loop to actually do the breakup -- read each value
inside in the macro variable PETVAL until no more;
%do %while(%scan(&petval,&i) ne );
data %scan(&petval,&i);
set &dsn;
where &dsv="%scan(&petval,&i)";
drop &dsv;
run;
*Increment the DO LOOP counter;
%let i=%eval(&i+1);
%end;
%mend breakup;
Now do the following call to the BREAKUP macro: %breakup(dsn=_allpet,dsv=pet); run; Notice that the same datasets as previous are created but the code used to create the dataset now has what is called macro parameters, i.e. DSN= and DSV=. Now lets say our dataset had a hundred PET values with a thousand NAME values -- just call the same macro! One important note here before you use the macro -- the values in the DSV parameter must not have spaces since the macro uses the spaces as delimiters between values in the macro variable PETVAL. So how can we change the macro again to handle this issue -- three lines need to be changed. Lets look first at the line: into :petval separated by " " If we were to change the line to into :petval separated by "~" then each value found in the variable given by the parameter DSV would be separated by a "~", so when scanning the macro variable in, then the two other lines that need to change are: data %scan(&petval,&i);
where pet="%scan(&petval,&i)";
to read data %scan(&petval,&i,%str(~));
where pet="%scan(&petval,&i,%str(~))";
So can we put this functionality into our macro -- short answer YES with one futher macro parameter: *A macro to actually do the subset;
%macro breakup(dsn= /*Dataset name*/
,dsv= /*Variable in dataset to use split by value*/
,vrs=%str( ) /*Values seperated in list by character -- default is a space*/
);
*Get a unique list of values;
proc sql noprint;
select distinct &dsv
into :petval separated by "&vrs"
from &dsn;
quit;
run;
*Initialize macro counter;
%let i=1;
*A DO loop to actually do the breakup -- read each value
inside in the macro variable PETVAL until no more;
%do %while(%scan(&petval,&i,&vrs) ne );
data %scan(&petval,&i,"&vrs");
set &dsn;
where &dsv="%scan(&petval,&i,"&vrs")";
drop &dsv;
run;
*Increment the DO LOOP counter;
%let i=%eval(&i+1);
%end;
%mend breakup;
Now take this and try it on some data -- edit the code and see what happens. Remember to have the MPRINT, MLOGIC and SYMBOLGEN options on. See you next month. MarchSometimes during development or when you are debugging a program, it is useful to have the LOG text and the OUTPUT text go to the same file. This can be done easily using the PRINTTO procedure as shown below: proc printto LOG='c:\temp\Program1.log' PRINT='c:\temp\Program1.log' new; run; *More SAS statements; proc printto; run; The first PRINTTO call will set the SAS LOG and SAS OUTPUT text to the file c:\temp\Program1.log, making sure that any existing output within the file is deleted by using the NEW option. If the NEW option is left off then any new LOG and OUTPUT is added to any existing text. See you next month. AprilSometimes we want to just access that single dataset off in a directory that is rarely has any useful data. Yes, it is possble to set a LIBNAME reference and then access that dataset as a two level name, but it is also possible to access it directly as the following example shows: data _weatherdata;
set 'c:\WeatherData\Boston\hightemperature.sas7bdat';
run;
See you next month. MayIf you have a large number of variables in a dataset, it is possible to reference ranges of variables without having to write out the long list. Lets look at some data and use this as an example: data _games0;
attrib year length=8 label='Year'
city length=$50 label='City'
nations length=8 label='Number of Nations'
athletes length=8 label='Number of Athletes';
infile cards;
input year city $ 9-30 nations athletes;
cards;
1896 Athens, Greece 14 241
1900 Paris, France 24 997
1904 St. Louis, United States 12 651
1908 London, Great Britain 22 2008
;
run;
title1 "Listing of Olympic Games, 1896-1908";
footnote1 "Source: Wikipedia, Accessed 2012-05-01";
run;
Now lets do a PROC CONTENTS run and see what we get (this is abridged to save space): Listing of Olympic Games, 1896-1908
The CONTENTS Procedure
Data Set Name WORK._GAMES0 Observations 4
Member Type DATA Variables 4
Engine V9 Indexes 0
Created Tuesday, May 01, 2012 09:04:12 AM Observation Length 80
Last Modified Tuesday, May 01, 2012 09:04:12 AM Deleted Observations 0
Protection Compressed NO
Data Set Type Sorted NO
Label
Data Representation WINDOWS_32
Encoding wlatin1 Western (Windows)
Variables in Creation Order
# Variable Type Len Label
1 year Num 8 Year
2 city Char 50 City
3 nations Num 8 Number of Nations
4 athletes Num 8 Number of Athletes
Source: Wikipedia, Accessed 2012-05-01
Note that I used the VARNUM option in the PROC CONTENTS call as this lists the variables out in order that they are in the dataset -- had we not used the VARNUM option variables would have been output in variable name order. The thing to look at here is the order of the variables -- first YEAR, then CITY, followed by NATIONS and ATHLETES. Now lets do a simple PROC PRINT with the variables to print being from YEAR to ATHLETES (this is a the same as doing a PROC PRINT without the VAR statement, but is used for illistrative reasons): proc print data=_games0 label noobs;
var year--athletes;
run;
and has the output: Listing of Olympic Games, 1896-1908
Number Number
of of
Year City Nations Athletes
1896 Athens, Greece 14 241
1900 Paris, France 24 997
1904 St. Louis, United States 12 651
1908 London, Great Britain 22 2008
Source: Wikipedia, Accessed 2012-05-01
Now lets say we want to print CITY then YEAR followed by NATIONS and ATHLETES: proc print data=_games0 label noobs;
var city--year nations athletes;
run;
When you run this an error similar to the following is displayed: ERROR: Starting variable after ending variable in data set. Why is SAS/WPS doing this? The simple answer is that when you use a range of variables, the procedure/datastep will use the order of the variables within the dataset, therefore in our example, the variable CITY came after YEAR which is reverse of the order in the dataset. This just touches the surface in regards to using variable ranges in your programming -- maybe sometime in the future I will expand on it. See you next month. JuneHow do we create a list of datasets in macro variable, and why would be need to do that? The second part of the question is easier to answer than the first part so I will answer this first -- we sometimes want to do an action on them, for example print out the first 25 observations of all datasets. Now the first part of the question -- how do we create a list of datasets in a macro variable? There are a number of ways to get a list of datasets in a library, the most common way being the use of the PROC CONTENTS, but another way is using what SAS already knows about our data in the SASHELP library, specifically the SASHELP.VMEMBER view -- this view contains a lot of information but the fields we are interested in are LIBNAME (library reference name), MEMNAME (name of dataset) and MEMTYPE (we only interested in datasets so am looking at the value 'DATA'). Lets first create the list of datasets and put that into a macro variable DSLIST using a datastep: data _null_;
length _txt $32000;
retain _txt;
set sashelp.vmember end=eof;
where libname='A2012';
_txt=catx(' ',_txt,memname);
if eof then call symput('DSLIST',strip(_txt));
run;
In the example above, the LENGTH statement sets the text field containing list of datasets; the RETAIN statement retains the list of dataset that are built across iterations of the datastep; in the SET statement, the variable EOF will equal 1 if end of the datastep is reached; the WHERE statement selects only datasets in the library A2012; the CATX function contactenates the dataset name found to the list of datasets; and finally when EOF=1, i.e. the last observation is found, the CALL SYMPUT function outputs the list of datasets to a macro variable DSLIST. Using PROC SQL the code is slightly different, as shown below: proc sql noprint;
select distinct memname
into :dslist separated by ' '
from sashelp.vcolumn
where libname='A2012';
quit;
run;
In the code above, the NOPRINT statement makes sure the SQL does not print out any output; the SELECT statement selects our data; the INTO statement puts the list into a macro variable DSLIST separated by a single space; from the dataset VCOLUMN in the libary SASHELP where the LIBNAME has the value A2012. The SQL code is certainly more readable from a readability point of view. Now that we have created our list of datasets, the next thing is to do something with them -- enter the macro DO loop processing. I prefer using a DO WHILE loop as I usually do not know how many values are in the macro variable, as shown below: %macro dotask;
%let i=1; *initialize internal counter;
%do %while(%scan(&dslist,&i) ne );
proc print data=A2012.%scan(&dslist,&i) (obs=25);
title1 "Listing of first 25 observation in dataset %scan(&dslist,&i)";
run;
%let %eval(&i+1); *Increment internal counter;
%end;
%mend dotask;
%dotask;
We can acutally know the number of values in a macro variable two ways, one being changing the datastep code slightly, as shown below: data _null_;
length _txt $32000;
retain _txt;
set sashelp.vmember end=eof;
where libname='A2012';
_txt=catx(' ',_txt,memname);
if eof then do;
call symput('DSLIST',strip(_txt));
call symput('DSNOBS',compress(put(_n_,8.))); *Number of obs passed as a macro variable;
end;
run;
or a method I introduced in August of 2011, the %SYSFUNC and COUNTW method: %let DSNOBS=%sysfunc(countw(&dslist)); the DOTASK macro can be amended to %macro dotask;
%do i=1 %to &dsnobs;
proc print data=A2012.%scan(&dslist,&i) (obs=25);
title1 "Listing of first 25 observation in dataset %scan(&dslist,&i)";
run;
%end;
%mend dotask;
%dotask;
Look at the differences and try this out on your own computer. See you next month. JulyCounting the number of COMPLETE months between two dates can be easily calculated using the following code: months = intck('month',date1,date2) - (day(date2) < day(date1))
The INTCK function counts the number of months between DATE1 and DATE2 using the MONTH value in the first parameter of the INTCK call. However, this can still cause a problem if the day number of the first date is after the day number of the second date which means a complete calendar month has not happened -- in order for this situation to resolve correctly, a check of this is made in the second part of the equation resulting in a '1' for TRUE and '0' for FALSE. See below for and example that shows this code in use: data _null_;
infile cards;
input @1 date1 date9. @11 date2 date9.;
months = intck('month',date1,date2) - (day(date2) < day(date1));
put date1= date9. date2= date9. months=;
cards;
14APR2012 13MAY2012
14APR2012 14MAY2012
14APR2012 15MAY2012
;
run;
date1=14APR2012 date2=13MAY2012 months=0
date1=14APR2012 date2=14MAY2012 months=1
date1=14APR2012 date2=15MAY2012 months=1
Try the code yourself and see how it works, and also see what happens if you take out the "(day(date2) < day(date1))", or swap the two dates. See you next month. AugustEver had to remove dummy records or subset data in an unknown number of datasets in a directory? This tip may well be useful for you. You could start with the following code: data but this is to time-consuming, especially if you have more than just a few datasets. Also it is up to the programmer to determine if the dataset actually has the variable needed for the condition to be true. Lets look at an example where a directory has around 100 datasets but we are only interested in working with datasets AE, COUNTRY, DM, EX, LB and VS. Note also that we cannot modify or delete the original datasets but we can work with them elsewhere, whether we copy the datasets from one directory to another, or work in the WORK library. For the purposes of this example we just want to bring these datasets to the WORK library and remove the dummy records, signified by the value '9999' in the variable SUBJID. Note also that the variable SUBJID is not in the dataset COUNTRY. The solution I am presenting here is just one method to solve this problem -- there are more "efficient" methods but this gives a good look at what is going on. First, lets copy the necessary datasets from a directory which I will call ORIGDATA: proc delete data=work._all_;
*Remove any existing datasets in the WORK directory;
proc copy in=ORIGDATA out=work;
*Copy datasets of interest to the WORK directory;
select AE COUNTRY DM EX LB VS;
run;
Now find out which datasets have the variable name SUBJID and put those datasets that have the variable in a set of macro variables: proc contents data=work._all_ out=_cont0 (keep=memname name) noprint;
*Get contents of the datasets in work area keeping only the dataset name
(MEMNAME) and variable name (NAME);
%let dslist0=0;
*Set internal counter with number of datasets with variable SUBJID to zero;
data _null_;
set _cont0 end=eof; *Use dataset with contents of datasets created above;
where name='SUBJID';
*Only interested in those datasets which have a variable called SUBJID;
call symput('dslist'||compress(put(_n_,best.)),compress(memname));
*Create a macro variable DSLISTn, where n is the occurence of the variable
SUBJID, containing the name of the dataset with the variable SUBJID;
if eof then call symput('dslist0',compress(put(_n_,best.)));
*If last record in dataset put count in macro variable DSLIST0;
run;
The dataset COUNTRY would not have been picked up for processing in the selection above as it did not contain the variable SUBJID, as in our original example. Now that we have our list of datasets with the variable SUBJID in macro variables it is now time to do the sub-setting using macro code: %do i=1 %to &dslist0;
*Fails if no datasets found with variable SUBJID, hence setting value
to zero initially;
data &&dslist&i;
*Macro resolution, first resolves the &i then resolves the &&dslist;
set &&dslist&i;
where subjid^='9999'; *Keeping data that SUBJID not equal to '9999';
run;
%end;
With this code it does not matter if there is one dataset or 1000 datasets in a library, the code will work for all those that meet a condition as spelt out in the original question. Hope this is useful. Have a safe and happy August. See you next month. SeptemberWhen I run some programs, I don't want all the program run to be reflected in the SAS LOG. An example is where I may bring in a FORMATS.SAS file that contains the formats I need to run in the program using a %INC statement. But what it I was to temporarily suspend output to the SAS LOG for a brief time? The following example will show a way of achieving this: %put Include FORMATS.SAS file; proc printto log="nul:" new; run; %inc ".\formats.sas"; run; proc printto log=LOG; run; The first PRINTTO call sends all LOG statements after this to a file that does not exist, includes the SAS program, then resets the LOG output to the default SAS LOG output file. This tip is useful if you have a large set of code that is always run and you do not want included in the SAS LOG. However it is always important to remember that not sending a program LOG to a file that you can read runs the risk of you not being able to see what a program is doing, therefore missing potentially serious programs that can affect the ouput. Hope this is useful. Have a safe and happy September. See you next month. OctoberWhen I want to get a list of formats in a formats catalog, without the detail of each format, the PROC CATALOG procedure is useful. The following code will produce a list of formats that are in the FORMATS library: proc catalog c=library.formats;
contents;
run;
If you have your formats stored in a WORK directory, i.e. these are not permanently saved formats in a permanent FORMATS catalog, the code would look something like this: proc catalog c=formats;
contents;
run;
Hope this is useful. Have a safe and happy October. See you next month. NovemberWhen including external SAS files in your program, the main way I see it done is using the %INCLUDE statement as shown below: %include "c:\temp\file1.sas"; %include "c:\temp\file2.sas"; In the case where you are bringing in a number of SAS programs from a single directory, a useful alturnative is to use a FILENAME statement that points to a single directory, and then use the %INCLUDE statement as shown below: filename inpgm "c:\temp"; %include inpgm(file1.sas); %include inpgm(file2.sas); run; Hope this is useful. Have a safe and happy November. See you next month. DecemberThere are a number of ways that the quantile can be calculated, but how do we find the mean, minimum and maximum for each quartile? For this we are going to look at some golf scores for a first round at a local championship game, where we are asked to find the quartiles and then find the mean, maximum and minimum ranges for each quartile. First our data: data golfscore;
infile cards;
input score @@;
cards;
66 65 73 72 89 65 64 89 68 66 69 62 87 72 72 88 83 79 66 62
77 84 81 68 64 84 89 74 76 87 67 83 69 72 64 90 80 65 64 70
71 78 70 79 63 71 67 86 69 74 86 78 89 79 70 73 84 64 90 63
;
run;
Now we find the four quartiles by using the PROC RANK procedure, where the GROUPS=4 option will split the scores in to four groups, outputting the original data with which quartile group the score belongs in. proc rank data=golfscore out=golfrank groups=4;
var score;
ranks rank_score;
run;
Next it is just a simple case of finding the mean, maximum and minimum scores for each quartile group by using the MEANS procedure as shown below: proc means data=golfrank nway mean min max;
class rank_score;
var score;
run;
which results in the following output: The MEANS Procedure
Analysis Variable : score
Rank for
Variable N
score Obs Mean Minimum Maximum
-------------------------------------------------------------------
0 15 64.2000000 62.0000000 66.0000000
1 16 69.8125000 67.0000000 72.0000000
2 15 77.8000000 73.0000000 83.0000000
3 14 87.2857143 84.0000000 90.0000000
-------------------------------------------------------------------
The output can be formatted a lot better than what is displayed in the default PROC MEANS, but our information is present - our lower quartile has as a mean of 64.2 and a range of 62 to 66. Have a safe and happy December. |