|
January
It is sometimes necessary to add a number
of variables in a record and create a variable containing
the total. How can we go about it.
Lets say we have the following data in a
dataset:
SubjectID x1 x2 x3
101 1 2 3
102 3 4 6
To get the total of x1, x2 and x3
it is possible to do the following:
However, what if we not only wanted
the SUM, but also the MEAN, MIN and MAX values? We would
have to write four references to the variables and would
appear like:
Sumx = sum(x1,x2,x3);
Meanx = mean(x1,x2,x3);
Minx = min(x1,x2,x3);
Maxx = max(x1,x2,x3);
But what if a variable x4 came
along, all four lines will have to be amended. Another
way is to put the variables into an array reference and
use OF processing, as the following example illustrates:
array z{*} x1 x2 x3;
Sumx = sum(of z{*});
Meanx = mean(of z{*});
Minx = min(of z{*});
Maxx = max(of z{*});
Thus if variable x4 suddenly came
into the mix then the only thing that would change is the
array reference, i.e.
For this example, I have used the
variables x1, x2 and x3, and have one feature – they
all start with the letter ‘x’. Due to this
feature it is possible to use the following code when
doing the function calls, as the following example
illustrates:
Try it and see.
February
When appending data for the first time, there is one
message that appears in the SAS LOG that some just try to
get rid of:
NOTE: BASE data set does not exist. DATA file is being copied to BASE file.
This occurs when the dataset represented in the BASE
option does not exist -- SAS will create this dataset
from the DATA option by copying the data from DATA to
BASE.
A trick I use to get rid of this SAS LOG message is to
create the BASE dataset with no observations from the
first dataset to be appended, as the following example
shows:
13 *--------------------------------------;
14 * Take the data from CLASS dataset in;
15 * the SASHELP library and split into;
16 * MALE and FEMALE;
17 *--------------------------------------;
18 data males;
19 set sashelp.class;
20 where sex='M';
21 run;
NOTE: There were 10 observations read from the data set SASHELP.CLASS.
WHERE sex='M';
NOTE: The data set WORK.MALES has 10 observations and 5 variables.
22 data females;
23 set sashelp.class;
24 where sex='F';
25 run;
NOTE: There were 9 observations read from the data set SASHELP.CLASS.
WHERE sex='F';
NOTE: The data set WORK.FEMALES has 9 observations and 5 variables.
26
27 *--------------------------------------;
28 * Create dataset COMBINED from MALES;
29 * with no observations;
30 *--------------------------------------;
31 data combined;
32 set males (obs=0);
33 run;
NOTE: There were 0 observations read from the data set WORK.MALES.
NOTE: The data set WORK.COMBINED has 0 observations and 5 variables.
34
35 *--------------------------------------;
36 * Now APPEND data;
37 *--------------------------------------;
38 proc append base=combined data=males force;
NOTE: Appending WORK.MALES to WORK.COMBINED.
NOTE: There were 10 observations read from the data set WORK.MALES.
NOTE: 10 observations added.
NOTE: The data set WORK.COMBINED has 10 observations and 5 variables.
39 proc append base=combined data=females force;
40 run;
NOTE: Appending WORK.FEMALES to WORK.COMBINED.
NOTE: There were 9 observations read from the data set WORK.FEMALES.
NOTE: 9 observations added.
NOTE: The data set WORK.COMBINED has 19 observations and 5 variables.
The purpose of the data step creating COMBINED before
the PROC APPEND call is to create the dataset COMBINED
with no observations -- this is a quick step as this will
only read in the variable information from the dataset
MALES. The two PROC APPEND calls after that bring the
data together. Without the datastep, and a note saying
that the BASE data set does not exist and that the DATA
file is being copied to BASE file will appear.
Hope this is useful to you.
March
There are times, when programming in SAS, where you
need to "delete all the datasets in the WORK
directory, except for these." I had such a situation
recently, where some datasets were being generated before
a program I was editing --I wanted to delete all except
these. Running the following code would delete ALL the
datasets in the WORK library:
proc datasets library=work mt=data kill;
quit;
run;
This was not what is wanted. One solution is to create
a create a list of datasets in the WORK directory,
removing those datasets that were not to be deleted, then
using that list in the DELETE statement inside a PROC
DATASETS call. Code like this could look like:
*** Create a list of members in the WORK library, except the one
that is needed to keep;
proc sql noprint;
select memname
into :mlist separated by ' '
from sashelp.vstable
where libname='WORK' and memname ^in('TREATMENT_MAPPING');
quit;
run;
*** Remove datasets in list;
proc datasets library=work mt=data;
delete &mlist
quit;
run;
Note that in the example above, the dataset
TREATMENT_MAPPING is not deleted in the WORK library, but
all other datasets are.
Another solution is to use the SAVE statement in the
DATASETS procedure, as the following example
demonstrates:
proc datasets library=work mt=data;
save TREATMENT_MAPPING;
quit;
run;
This last solution is more easier to write and
maintain.
This month the SAS Global Forum is held at the Gaylord
National Resort and Convention Center, at National Harbor
in Maryland. If you are lucky enough to go, then please
use this event, the premier event on the SAS Calendar, to
hear what others are doing at the presentations, network,
have some fun, and take back to your own groups of some
lessons learned. If you cannot go, keep an eye on the SAS
website for the videos of selected segments of the
conference, and don't miss the papers presented at
conference -- there will be a nugget or two for everyone.
April
Last month I presented a paper at SAS Global Forum on
merging data eight different ways (the link to the paper
is here). One of the common
questions raised during the Question and Answer session
around the posters was "You show for one variable,
TRT_CODE, but lets say I want to add multiple variables,
like AGE and SEX, with the PROC FORMAT statement?"
This month I will address that question.
Lets first look at some data -- for this example I
will load the data using a CARDS statement:
data PATDATA;
infile CARDS;
input SUBJECT $ 1-6
TRT_CODE $ 8
AGE 10-11
SEX $ 13;
cards;
124263 A 25 M
124264 A 31 F
124265 B 30 M
124266 B 26 F
;
run;
data ADVERSE;
infile CARDS;
input SUBJECT $ 1-6
EVENT $ 8-40;
cards;
124263 HEADACHE
124266 FEVER
124266 NAUSEA
124267 FRACTURE
run;
When merging the data this way, it is necessary to
work out which dataset has a unique key for the merging
variable -- in this case the dataset PATDATA is unique.
The next step is create the format from the PATDATA
dataset. The following code will do this:
*** To create a format from a dataset there needs to be four variables
present -- FMTNAME (format name), TYPE (type of format, usually
(C)haracter or (N)umeric), START (value), and LABEL (decoded value).
For this example will also create a HLO variable -- this will signal
that OTHER is to be set, and for our purpose it will be set to blank
(this makes sure that subjects in ADVERSE not in PATDATA have a
blank value coming through.;
data FMT;
*** Define START (subject number), out LABEL variable (this will
contain TRT_CODE, AGE and SEX) and HLO (OTHER flag);
length START $8 LABEL $200 HLO $1;
*** FMTNAME is the Format Name, and the TYPE is character;
retain FMTNAME 'TRT_FMT' TYPE 'C';
*** Get our data and create the START and LABEL variable;
set PATDATA end=EOF;
START=cats(SUBJECT);
LABEL=catx('|',TRT_CODE,put(AGE,best.),SEX);
*** Output each record;
output;
*** Create one last record to deal with OTHER;
if EOF then do;
START='**OTHER**';
call missing(LABEL);
HLO='O';
output;
end;
run;
*** Create the format;
proc format cntlin=FMT;
run;
*** Do the merge;
data ALLDATA0;
*** Read in each record of ADVERSE dataset;
set ADVERSE;
*** Create the variables to contain Treatment Code
(TRT_CODE), Age (AGE) and SEX (Sex);
attrib TRT_CODE length=$1 label='Treatment Code'
AGE length=8 label='Age (years)'
SEX length=$1 label='Sex';
*** Now get TRT_CODE, AGE and SEX from format,
using the SCAN function to get the correct data
into the variable. Will use a temporary variable
_TMP to contain the value from format. Also, will
only need to extract if format comes across.;
length _TMP $200;
drop _TMP;
_TMP=put(SUBJECT,$TRT_FMT.);
if ^missing(strip(_TMP)) then do;
TRT_CODE=scan(_tmp,1,'|');
AGE=input(scan(put(subject,$trt_fmt.),2,'|'),best.);
SEX=scan(put(SUBJECT,$TRT_FMT.),3,'|');
end;
run;
Using the PROC FORMAT as in this example is a very
interesting method to merge two datasets, from a
one-to-one or one-to-many. I have found in the past that
the efficiency of this method increases as the number of
observations increase when compared against either a
MERGE statement inside a datastep or using PROC SQL.
I hope you find this interesting.
May
This month I am going to talk about dataset variable
labels, and why they are useful.
There are many parts to a variable, key among them are
the name of the variable (NAME), type (TYPE), and length
(LENGTH). Under SAS version 8 and above the name of a
variable can be 32 characters long, its type can be
either Character or Numeric, and the length for a
character variable can be set between 1 and 32767
characters while a numeric variable can be set between 3
and 8 (yes, some operating systems allow for 2 but not
all).
There are other parts to a variable, but the three
most common are the informat, format and label -- we
shall look at this last part this month.
What exactly is a label and why is it used? In simple
terms the label is a short descriptive text giving a more
user friendly description of the variable. Take for
example a very simple example, the variable WEIGHT in a
dataset -- sure it tells me what the variable is (it is
weight) but it does not tell me other things I may need
to know like what unit it is in or when the weight data
point was collected, all of this being very important
information. Given that I am allowed 32 characters for a
variable name I could change the variable name to reflect
this information, but in most cases it is not possible to
do this and it indeed impractical. So here comes the use
of the LABEL statement.
The label itself can be 256 characters, including
blanks, in length (40 characters if you are still using
SAS version 6.xx) -- this gives us plenty of room to
write a good description of the variable. Setting the
label is commonly achieved by using the LABEL statement
inside a datastep or the LABEL option under the MODIFY
statement in the DATASETS procedure, the syntax of which
is given below:
*** Inside a datastep;
data class;
set class;
label weight='Weight (kg) at Start of Study';
run;
*** Inside the DATASETS procedure;
proc datasets library=work;
modify class;
label weight='Weight (kg) at Start of Study';
quit;
run;
I personally use the datastep method if I am creating
or modifying a variable inside that datastep, otherwise I
use the DATASETS procedure, but is only my convention --
there is no set rule with this.
Before going on to a real world example, lets first
see how we would look at what labels are set, if any,
inside a dataset.
The two easiest ways to look at the data is to either
run a CONTENTS procedure call on the dataset, or if you
have the SAS Viewer installed, look at the attributes
window for that dataset.
Now lets look at a real world example where it will
all come clearer. For this example I will use the dataset
SASHELP.CLASS. Lets look first at the structure of the
dataset using the CONTENTS procedure (will actually make
a copy first so I don't overwrite the original data):
data class; *** Make a copy of the dataset;
set sashelp.class;
run;
proc contents data=class; *** Get structure of the dataset;
run;
Running this code we get the following output
(abridged):
Alphabetic List of Variables and Attributes
# Variable Type Len
3 Age Num 8
4 Height Num 8
1 Name Char 8
2 Sex Char 1
5 Weight Num 8
If a label existed for a variable we would see a
column headed "Label", but in this case there
is no labels applied to the dataset. So now lets set one
for WEIGHT as we indicated above (this time I shall do
the content structure not from the CONTENTS procedure,
but the CONTENTS statement inside the DATASETS
procedure):
proc datasets library=work;
modify class;
label weight='Weight (kg) at Start of Study';
contents data=class;
quit;
run;
Running this code we get the following output
(abridged):
Alphabetic List of Variables and Attributes
# Variable Type Len Label
3 Age Num 8
4 Height Num 8
1 Name Char 8
2 Sex Char 1
5 Weight Num 8 Weight (kg) at Start of Study
As you can see the variable WEIGHT now has a label --
the reason why it is useful will become clear shortly.
For the purposes of this example I will also now add a
label to the variable AGE and HEIGHT using the DATASETS
procedure as above:
proc datasets library=work;
modify class;
label height='Height (in) at Start of Study'
age='Age (years) at Start of Study';
contents data=class;
quit;
run;
Note that I did not redo the label for the variable
WEIGHT in the above code as it was already done
previously, although I could have put in the step and
even replaced it with new text.
Now why is the label useful. As you can see already it
gives a useful description of what the variable is. Now
lets extend that to a small report using the PRINT
procedure:
proc print data=class noobs;
run;
that produces the following output (abridged):
Name Sex Age Height Weight
Alfred M 14 69.0 112.5
Alice F 13 56.5 84.0
Barbara F 13 65.3 98.0
Carol F 14 62.8 102.5
Henry M 14 63.5 102.5
The column headers are the same as the variable names,
and we have the same problem of not knowing what the
units are for Age, Height and Weight (but did we not put
that in a label earlier) but lets add another option to
get the labels:
proc print data=class noobs LABEL;
run;
that produces the following output (abridged):
Age (years) Height (in) Weight (kg)
at Start at Start at Start
Name Sex of Study of Study of Study
Alfred M 14 69.0 112.5
Alice F 13 56.5 84.0
Barbara F 13 65.3 98.0
Carol F 14 62.8 102.5
Henry M 14 63.5 102.5
Now, as you can see, we have a report that gives a
clear description of the variables though the use of
dataset variable labels.
There will be those reading this who will say that I
could have put the label in the PRINT procedure call
(yes, the LABEL statement is a global statement that can
be used in almost any procedure) and would be done this
way using the following code:
proc print data=class noobs LABEL;
label weight='Weight (kg) at Start of Study'
height='Height (in) at Start of Study'
age='Age (years) at Start of Study';
run;
But the one reason I don't normally use this method is
that the label does not carry forward in the dataset. Now
see a complete example where the CLASS data is copied
from the SASHELP directory, useful labels put on the
dataset, then doing a PRINT, MEANS and TABULATE call on
the same data, all with the same labels:
proc datasets library=work;
copy in=sashelp out=work;
select class;
modify class;
label weight='Weight (kg) at Start of Study'
height='Height (in) at Start of Study'
age='Age (years) at Start of Study';
quit;
run;
proc print data=class noobs LABEL;
run;
proc means data=class;
var age height weight;
run;
proc tabulate data=class;
class sex;
var age height weight;
tables age*
(n*f=8.0 mean*f=8.2 std*f=8.3
median*f=8.2 min*f=8.0 max*f=8.0)
(height weight)*
(n*f=8.0 mean*f=8.3 std*f=8.4
median*f=8.3 min*f=8.1 max*f=8.1),
sex all='Total';
run;
produces the following output:
The PRINT Procedure
Age (years) Height (in) Weight (kg)
at Start at Start at Start
Name Sex of Study of Study of Study
Alfred M 14 69.0 112.5
Alice F 13 56.5 84.0
Barbara F 13 65.3 98.0
Carol F 14 62.8 102.5
Henry M 14 63.5 102.5
James M 12 57.3 83.0
Jane F 12 59.8 84.5
Janet F 15 62.5 112.5
Jeffrey M 13 62.5 84.0
John M 12 59.0 99.5
Joyce F 11 51.3 50.5
Judy F 14 64.3 90.0
Louise F 12 56.3 77.0
Mary F 15 66.5 112.0
Philip M 16 72.0 150.0
Robert M 12 64.8 128.0
Ronald M 15 67.0 133.0
Thomas M 11 57.5 85.0
William M 15 66.5 112.0
The MEANS Procedure
Variable Label N Mean Std Dev Minimum Maximum
---------------------------------------------------------------------------------------------------
Age Age (years) at Start of Study 19 13.3157895 1.4926722 11.0000000 16.0000000
Height Height (in) at Start of Study 19 62.3368421 5.1270752 51.3000000 72.0000000
Weight Weight (kg) at Start of Study 19 100.0263158 22.7739335 50.5000000 150.0000000
---------------------------------------------------------------------------------------------------
The TABULATE Procedure
----------------------------------------------------
| | Sex | |
| |-----------------| |
| | F | M | Total |
|-----------------------+--------+--------+--------|
|Age (years)|N | 9| 10| 19|
|at Start of|-----------+--------+--------+--------|
|Study |Mean | 13.22| 13.40| 13.32|
| |-----------+--------+--------+--------|
| |Std | 1.394| 1.647| 1.493|
| |-----------+--------+--------+--------|
| |Median | 13.00| 13.50| 13.00|
| |-----------+--------+--------+--------|
| |Min | 11| 11| 11|
| |-----------+--------+--------+--------|
| |Max | 15| 16| 16|
|-----------+-----------+--------+--------+--------|
|Height (in)|N | 9| 10| 19|
|at Start of|-----------+--------+--------+--------|
|Study |Mean | 60.589| 63.910| 62.337|
| |-----------+--------+--------+--------|
| |Std | 5.0183| 4.9379| 5.1271|
| |-----------+--------+--------+--------|
| |Median | 62.500| 64.150| 62.800|
| |-----------+--------+--------+--------|
| |Min | 51.3| 57.3| 51.3|
| |-----------+--------+--------+--------|
| |Max | 66.5| 72.0| 72.0|
|-----------+-----------+--------+--------+--------|
|Weight (kg)|N | 9| 10| 19|
|at Start of|-----------+--------+--------+--------|
|Study |Mean | 90.111| 108.950| 100.026|
| |-----------+--------+--------+--------|
| |Std | 19.3839| 22.7272| 22.7739|
| |-----------+--------+--------+--------|
| |Median | 90.000| 107.250| 99.500|
| |-----------+--------+--------+--------|
| |Min | 50.5| 83.0| 50.5|
| |-----------+--------+--------+--------|
| |Max | 112.5| 150.0| 150.0|
----------------------------------------------------
Note that the label we defined in the DATASETS
procedure carried forward to the PRINT, MEANS and
TABULATE procedures.
I hope you find this months tip useful.
June
While at PharmaSUG I was asked a question as to how a percentage change value could be displayed prefixed by a '+' or '-'. This can be done in a picture format the following way:
proc format;
picture plusminus
low-<0 = '0000.0' (prefix='-')
0 = '0' (noedit)
0<-high = '0000.0' (prefix='+');
run;
Note that in this example we are displaying the values to one decimal place.
Now lets look at an example, displaying the Change from Baseline Percentage value to one decimal place:
*--------------------------------------;
* Set up display of Cycle/Day in report;
*--------------------------------------;
proc format;
value $CycDay
'C1D2' = 'Cycle 1, Day 2'
'C1D3' = 'Cycle 1, Day 3'
'C2D1' = 'Cycle 2, Day 1'
'C2D2' = 'Cycle 2, Day 2'
'C2D3' = 'Cycle 2, Day 3';
run;
*--------------------------------------;
* For the purposes of this example, the;
* values are coming in from a CARDS ;
* statement. ;
*--------------------------------------;
data BUN_ChangeFromBaseline;
attrib TimePoint length=$4 format=$CycDay.
label='TimePoint'
PercentChange length=8 format=plusminus.
label='% Change from Baseline';
infile cards;
input TimePoint $ PercentChange;
cards;
C1D2 -8.6
C1D3 3.5
C2D1 4.7
C2D2 -3.7
C2D3 -5.9
;
run;
*--------------------------------------;
* Make sure order is correct. ;
*--------------------------------------;
proc sort data=BUN_ChangeFromBaseline;
by TimePoint;
run;
*--------------------------------------;
* Generate listing ;
*--------------------------------------;
options byline nocenter;
run;
title1 "Listing of Percentage Change from Baseline for BUN values, at Timepoint";
proc print data=BUN_ChangeFromBaseline noobs label;
var TimePoint PercentChange;
run;
The following output will appear in the Output Window:
Listing of Percentage Change from Baseline for BUN values, at Timepoint
% Change
from
TimePoint Baseline
Cycle 1, Day 2 -8.6
Cycle 1, Day 3 +3.5
Cycle 2, Day 1 +4.7
Cycle 2, Day 2 -3.7
Cycle 2, Day 3 -5.9
Note that the raw values were not changed into a text variable for display, as programs sometimes do.
I hope you find this months tip useful.
July
If I have a character date of '6JUN2009', will this be read the same way as a character date of '06JUN2009'? This was a real life question, the simple answer being 'yes', as the following code demonstrates:
data _null_;
txtdate1='6JUN2009';
numdate1=input(txtdate1,date9.);
put 'Test Date 1: ' txtdate1= numdate1= date9.;
txtdate2='06JUN2009';
numdate2=input(txtdate2,date9.);
put 'Test Date 2: ' txtdate2= numdate2= date9.;
run;
Test Date 1: txtdate1=6JUN2009 numdate1=06JUN2009
Test Date 2: txtdate2=06JUN2009 numdate2=06JUN2009
This question actually come up when partial date variables (this is where the day, month and year for a particular event are in three separate variables) existed in a dataset and a date is constructed from this. If the three variables are numeric then it is possible to use the MDY function directly, as the following code fragment suggests:
StartDate=MDY(Start_Month,Start_Day,Start_Year);
But what if one or more of the three partial date variables are character? In this case the month variable is almost always character and has the first three letters for the month -- in this case we can certainly use an informat for the month, do a character to numeric conversion for the character variables, and then execute the function.
There is another better way though in this case, and that is to use a CATS function then an INPUT function with a format, as the following example demonstrates:
StartDate=INPUT(CATS(Start_Day,Start_Month,Start_Year),DATE9.);
If there are any numeric to character conversions that occur then this should be dealt with inside the CATS function.
Now lets look at an example:
DATA aedata0;
INFILE cards;
INPUT subjid $ 1-4 event $ 6-20 start_d $ 22-23 start_m $ 25-27 start_y $ 29-32;
ATTRIB start_dt LENGTH=8 FORMAT=date9.;
start_dt=INPUT(CATS(start_d,start_m,start_y),date9.);
PUT (_ALL_) (=);
CARDS;
0123 HEADACHE 13 MAR 2009
0123 FEVER 01 APR 2009
0123 NAUSEA 8 APR 2009
;
RUN;
When the code is run the following appeared in the SAS LOG:
69 DATA aedata0;
70 INFILE cards;
71 INPUT subjid $ 1-4 event $ 6-20 start_d $ 22-23 start_m $ 25-27 start_y $ 29-32;
72 ATTRIB start_dt LENGTH=8 FORMAT=date9.;
73 start_dt=INPUT(CATS(start_d,start_m,start_y),date9.);
74 PUT (_ALL_) (=);
75 CARDS;
subjid=0123 event=HEADACHE start_d=13 start_m=MAR start_y=2009 start_dt=13MAR2009
subjid=0123 event=FEVER start_d=01 start_m=APR start_y=2009 start_dt=01APR2009
subjid=0123 event=NAUSEA start_d=8 start_m=APR start_y=2009 start_dt=08APR2009
NOTE: The data set WORK.AEDATA0 has 3 observations and 6 variables.
79 ;
80 RUN;
Even though the START_D variable had both a zero led value (observation 2) and a single value (observation 3), SAS still worked out the correct START_DT value.
August
This month is a small tip on selecting data for a report, more specifically, a request for a report based on a particular month’s data. I had such a request last month and I thought I would pass on a little trick that can help solve such a question.
Let’s look at the following data:
data sales;
format date date9.;
input date date9. value;
cards;
29JUN2009 500
06JUL2009 490
13JUL2009 480
20JUL2009 510
27JUL2009 520
03AUG2009 530
;
run;
Our request is to print out and give the total value of sales for July 2009. For the purposes of this example I am going to use a simple PROC PRINT call with a SUM statement to give the sum this data, but how about the selection of the records for the month of July?
One way to subset the data for the month of July is to use a WHERE statement in the following form:
WHERE 01JUL2009<=date<=31JUL2009;
but, is there a solution where we do not have to worry about the number of days in the month? SAS does give one method, and that is by the use of the format MONYY, as the following WHERE statement demonstrates:
WHERE PUT(date,monyy7.)="JUL2009";
Now putting this together, we have the following SAS code and output using the PRINT procedure:
title1 "Listing of Sales for July 2009";
proc print data=sales noobs;
var date value;
sum value;
where put(date,monyy7.)="JUL2009";
run;
Listing of Sales for July 2009 08:40 Thursday, August 6, 2009 1
date value
06JUL2009 490
13JUL2009 480
20JUL2009 510
27JUL2009 520
=====
2000
Using this trick in the WHERE statement allows us not to worry about the number of days in the month (are there 28 or 29 days in February this year?). Of course I could have also used a number of other formats including YYMM and MMYY to do the same -- refer to your local documentation for other formats that are available. Extending this, you could use a format like YYQ for calendar quarterly reports, or YEAR format for annual calendar year reports.
Hope you find this useful.
September
This month is a little trick with CALL EXECUTE to compare, using PROC COMPARE, all the datasets in one directory with those in another -- don't be scared about using CALL EXECUTE, it is just another tool for which we can use to make our programs more efficient.
One typical approach is to write all the write out all the PROC COMPARE call separately, as the following code demonstrates:
libname source_data "source_data_library";
Libname compare_data "compare_data_library";
PROC COMPARE BASE=source.adae COMPARE=compare.adae;
run;
PROC COMPARE BASE=source.addm COMPARE=compare.addm;
run;
PROC COMPARE BASE=source.advs COMPARE=compare.advs;
run;
For the purposes of this example, there are only three datasets in the directory we are comparing.
Now lets look at automating this a bit by getting SAS to create the list of dataset that we are comparing and using CALL EXECUTE to use this list and actually do the dataset compare.
The first thing to note that during your SAS session, SAS actually knows a lot about your data. One of the pieces of information that is available is the name of the datasets that the session has access to - this can be found in the view (this is like a window looking at a much larger picture) called VMEMBER located in the SASHELP library.
For this approach to work we have to choose one library as the master which is the directory that has the primary list of datasets that we are comparing - this will be used as the list of dataset that is going to be compared. There is also the directory that has the data we are comparing.
Now lets write some code:
*** Lets define the location of the source and compare data;
libname source_data "source_data_library";
libname compare_data "compare_data_library";
data _null_;
*** Lets get the list of datasets in the SOURCE_DATA
library - that getting datasets only by use of the
MEMTYPE="DATA" statement in the WHERE clause;
set sashelp.vmember;
where libname="SOURCE_DATA" And memtype="DATA";
*** And now the CALL EXECUTE - this will take the list
of datasets in the SOURCE_DATA library and use that
list to write the PROC COMPARE calls;
CALL EXECUTE("proc compare base=source_data."||strip(memname)||
" compare=compare_data."||strip(memname)||"; run;");
run;
The hardest part to understand is the CALL EXECUTE, and the second is how we got the list from the VMEMBER view in the SASHELP library - note that the SASHELP library is a default SAS library that is initiated when SAS starts so there is no need to use a LIBNAME statement to define it.
Take this code and try it at your site - after a short time you will understand the code and how the CALL EXECUTE works in this example.
October
This month is about the use and abuse of the NODUPKEY option in the SORT procedure.
First, lets discuss what exactly is the NODUPKEY option? Referring to the SAS Online help, then NODUPKEY option is:
checks for and eliminates observations with duplicate BY values
So why, you may ask, did this discussion come about. I was looking at some code recently and the programmer used a NODUPKEY with a WHERE clause to select some data for analysis -- this would only work successfully if it was known that there was a unique "list" for the variables concerned.
Lets look at some data that I will use as an example.
DATASET: SHOPDATA
SHOP_NUMBER BEAN_COLOR COUNT
001 BLUE 5
003 GREEN 2
002 RED 4
003 RED 3
001 GREEN 6
002 RED 2
002 BLUE 1
003 GREEN 5
001 RED 2
This is not sorted (our data is not regularly sorted in a form that we can use) but we can see in this small set of data that there are two GREEN bean records for 003.
Now lets construct a unique set of SHOP_NUMBER and BEAN_COLOR:
proc sort data=shopdata (keep=shop_number bean_color)
out=uniquelist nodupkey;
by shop_number bean_color;
run;
The code above will create a dataset with a unique set of SHOP_NUMBER and BEAN_COLOR values.
But what of the situation where we create the list of SHOP_NUMBER and BEAN_COLOR values using the following code:
proc sort data=shopdata out=uniquelist nodupkey;
by shop_number bean_color;
run;
and then use the following code to find the mean number of beans per shop:
proc means data=uniquelist mean;
class shop_number;
var count;
run;
The COUNT variable exists in the dataset UNIQUELIST (it has not been dropped) so the PROC MEANS step will be valid, but what value for COUNT does the step use for SHOP_NUMBER=003 and BEAN_COLOR=GREEN? Is it value 5 or 2? The actual answer is that there is no real way to determine which value is used, sometimes it will be 5 and sometimes it will be 2. The only way to get around this is to be explicit in the selection of records where there may be a multiple. Again, be aware that this is based on an actual example of code so even the best of us get it wrong occasionally.
The lesson of this story is do not use the NODUPKEY option in the SORT procedure unless you are creating a unique list of values based on a variable list, but most of all do not use this list (unless you can be absolutely certain that there is no chance of duplicates) for analysis purposes.
Another option inside the SORT procedure you may have heard of or used is NODUPRECS (also known as NODUP) which has the following in the SAS Online help:
checks for and eliminates duplicate observations
The major difference between the NODUPRECS and the NODUPKEY is that the NODUPKEY uses the variables in the BY statement to identify the unique observations, as opposed to the NODUPRECS option that finds unique records based on all the variable values for the entire record -- in our data above, no records would be removed as they are unique.
I hope this is useful. See you next month.
November
This month I am looking at the DO statement - it its simplest form this specifies a set of statements that are to be done until a matching END statement appears.
There are five basic forms of the DO statement:
- DO
- interactive DO
- DO WHILE
- DO UNTIL
- DO OVER
The simple form of the DO statement is:
DO;
*More SAS statements;
END;
This version of the DO statement is more often seen in the selection of statements that are run if a condition is true:
IF condition THEN DO;
*More SAS statements;
END;
Before looking at the interactive way the DO statement is used, I will change things around from what you see in most textbooks and talk about two forms that use expressions to determine when the loops stops - the DO WHILE and DO UNTIL.
The first is the DO WHILE statement that carries out a set of statements while a condition is true - this has the form:
DO WHILE(expression);
*More SAS statements;
END;
The second is the DO UNTIL statement that carries out a set of statements until a condition is true - this has the form:
DO UNTIL(expression);
*More SAS statements;
END;
For the WHILE and UNTIL, the expression (an expression is a trail of operands and/or operators that result in a TRUE or FALSE answer) is evaluated. In the case of a DO WHILE, the expression is evaluated at the top the loop, while in the DO UNTIL form the expression is evaluated at the bottom of the loop. The following code will show its use and the difference between these two forms of the DO:
1 data _null_;
2 put 'Example using DO WHILE:';
3 i=0;
4 do while(i<5);
5 put @3 i=;
6 i+1;
7 end;
8
9 put 'Example using DO UNTIL:';
10 i=0;
11 do until(i=5);
12 put @3 i=;
13 i+1;
14 end;
15 run;
Example using DO WHILE:
i=0
i=1
i=2
i=3
i=4
Example using DO UNTIL:
i=0
i=1
i=2
i=3
i=4
Note that a DO UNTIL loop will always do at least one iteration since it is evaluated at the end of the loop. A word of warning though when using the DO WHERE or DO UNTIL - if the expression is never satisfied then the loop will never stop.
The Interactive DO statement uses a variable for an index value, and the SAS code between the DO and END statements is repeated for each value of the index variable. There are a couple of versions of this which are shown below:
DO index_variable = start_value TO stop_value;
*More SAS statements;
END;
DO index_variable = start_value TO stop_value BY by_value;
*More SAS statements;
END;
Each of these two versions above can be extended with the use of a WHILE or UNTIL as detailed above.
Another version of this is where the values for the INDEX_VARIABLE are specified:
DO index_variable = value1, value2, valuen;
*More SAS statements;
END;
The last form of the DO statement that I will look at here is the DO OVER statement - this will action the statements within the loop for all the elements defined within an array, as the following example shows:
ARRAY monthval {12} jan feb mar apr may jun jul aug sep oct nov dec;
DO OVER monthval;
*More SAS statements;
END;
Below are some examples of valid DO loops:
do i=1 to 10;
do i=1 to 10 by 2;
do i=0 to 10 by 3 while(x=y);
do i=1,6,3;
do i=1 to 10, 15, 20 to 16 by -1;
do while(i<20);
do until(i>=20);
do i='01JAN2009'd to '31DEC2009'd;
To prematurely break out of a DO loop there are two ways of doing this - this is actually very useful to make sure that an infinite loop is not created.
The first way is the use of the LEAVE statement that stops the processing of the DO loop and continues with the next statement following the END of the DO loop. The following demonstrates its use:
leaving_bonus=0;
DO i=1 TO years_of_service;
leaving_bonus+50;
** Stop if Leaving Bonus > 500;
IF leaving_bonus>=500 THEN LEAVE;
END;
Another way is to use a flag variable - if the flag is 'OFF' then continue, else if 'ON' then stop:
leaving_bonus=0;
stopflag=0; ** Flag variable;
DO i=1 TO years_of_service WHILE(stopflag=0);
leaving_bonus+50;
** Stop if Leaving Bonus > 500;
IF leaving_bonus>=500 THEN stopflag=1;
END;
In both of the methods above, the flow of step will continue to any statements set after the END statement associated with the DO statement.
DO loops of course can be nested, that is have a DO loop within a DO loop - the number of loops within a loop you can write is for all practical purposes unlimited, but it would be very have to leave if you had too many levels! See the following for an example of a nested DO loop:
DO years=1990 TO 2009;
DO months=1 TO 12;
*More SAS statements;
END;
END;
I hope the tip for this month has taught you something about the DO statement. As always, I ask you to take the code and try it out for yourself - this is the best way to learn.
See you all in December.
December
I recently had an email from Nancy in New Jersey on how to do a many-to-many merge in SAS without using SQL, so for this month is a discussion on this issue that often occurs when dealing with data.
Lets look at some data first -- this is in the form of SAS card statements (you could also use a DATALINES statement, but for this example I am using the older CARDS statement) so you can copy this data an load it into your SAS Program window:
*----------------------------------------------------*;
* Lets load some data;
*----------------------------------------------------*;
data ae; ** Adverse Event Data;
infile cards;
input ptnum $ 1-3 @5 date date9. event $ 15-35;
format date date9.;
cards;
001 16NOV2009 Nausea
002 16NOV2009 Heartburn
002 16NOV2009 Acid Indigestion
002 18NOV2009 Nausea
003 17NOV2009 Fever
003 18NOV2009 Fever
005 17NOV2009 Fever
;
run;
data cm; ** Concomitant Medication Data;
infile cards;
input ptnum $ 1-3 @5 date date9. medication $ 15-35;
format date date9.;
cards;
001 16NOV2009 Dopamine
002 16NOV2009 Antacid
002 16NOV2009 Sodium bicarbonate
002 18NOV2009 Dopamine
003 18NOV2009 Asprin
004 19NOV2009 Asprin
005 17NOV2009 Asprin
;
run;
The merge that is going to be done here is a merge by PTNUM (subject number) and DATE.
Some notes on the data:
- Subjects 001 and 005 have a one-to-one match.
- Subject 002 has a many to many match on 16NOV2009, but a one to one match on 18NOV2009.
- Subject 003 has no match for 17NOV2009 but does have a match for 18NOV2009.
- Subject 004 has no match.
Lets jump ahead to what we are looking for when we merge the two datasets -- note that we are doing an inner join, i.e. where there is no match the observation is excluded:
Obs ptnum date event medication
1 001 16NOV2009 Nausea Dopamine
2 002 16NOV2009 Heartburn Antacid
3 002 16NOV2009 Heartburn Sodium bicarbonate
4 002 16NOV2009 Acid Indigestion Antacid
5 002 16NOV2009 Acid Indigestion Sodium bicarbonate
6 002 18NOV2009 Nausea Dopamine
7 003 18NOV2009 Fever Asprin
8 005 17NOV2009 Fever Asprin
When first looking at merging the two datasets, the first thought may be to use a simple MERGE statement as shown below:
** SAS Code;
*----------------------------------------------------*;
* This will not work!;
*----------------------------------------------------*;
data all0;
merge ae cm;
by ptnum date;
run;
title1 "Merge using the MERGE statement -- this fails";
proc print data=all0;
run;
** SAS Output;
Merge using the MERGE statement -- this fails
Obs ptnum date event medication
1 001 16NOV2009 Nausea Dopamine
2 002 16NOV2009 Heartburn Antacid
3 002 16NOV2009 Acid Indigestion Sodium bicarbonate
4 002 18NOV2009 Nausea Dopamine
5 003 17NOV2009 Fever
6 003 18NOV2009 Fever Asprin
7 004 19NOV2009 Asprin
8 005 17NOV2009 Fever Asprin
As can be seen, this clearly does not work, so how can a many to many merge be done successfully?
The most common way that this match is done is with an SQL call, as the following code demonstrates:
** SAS Code;
*----------------------------------------------------*;
* SQL, the most common way that it is seen done;
*----------------------------------------------------*;
proc sql;
create table all0 as
select a.*, b.medication
from ae a inner join cm b
on a.ptnum=b.ptnum and
a.date=b.date;
quit;
run;
title1 "Merge using SQL -- most common way this is seen done";
proc print data=all0;
run;
** SAS Output;
Merge using SQL -- most common way this is seen done
Obs ptnum date event medication
1 001 16NOV2009 Nausea Dopamine
2 002 16NOV2009 Heartburn Antacid
3 002 16NOV2009 Heartburn Sodium bicarbonate
4 002 16NOV2009 Acid Indigestion Antacid
5 002 16NOV2009 Acid Indigestion Sodium bicarbonate
6 002 18NOV2009 Nausea Dopamine
7 003 18NOV2009 Fever Asprin
8 005 17NOV2009 Fever Asprin
This works, but it is not what we want -- we do not want to use SQL in the solution.
This brings about the use of the POINT option in the SET statement -- take a look at the following code and try it out in your Program window:
** SAS Code;
*----------------------------------------------------*;
* The datastep with POINT option -- not often seen,;
* but gives most control. Key to this is that the;
* datastep takes each observation in AE, and then;
* tries to match this with each observation in;
* CM -- this is basically a loop within a loop!;
*----------------------------------------------------*;
data all1;
set ae;
drop _:; ** Drop temporary variables;
match=0; ** Match flag;
** Our loop within a loop -- output if match;
do i=1 to xnobs;
** Need to rename the "merging" variables within the CM
dataset;
set cm (rename=(ptnum=_ptnum date=_date)) nobs=xnobs point=i;
** Have to rename matching variables so that they do not overwrite
the original values in AE;
if ptnum=_ptnum and date=_date then do;
match=1; ** Yes, there is a match my the "merging" variables;
output;
end;
end;
run;
title1 "Merge using using the POINT option in a SET statement";
proc print data=all1;
run;
** SAS Output;
Merge using using the POINT option in a SET statement
Obs ptnum date event match medication
1 001 16NOV2009 Nausea 1 Dopamine
2 002 16NOV2009 Heartburn 1 Antacid
3 002 16NOV2009 Heartburn 1 Sodium bicarbonate
4 002 16NOV2009 Acid Indigestion 1 Antacid
5 002 16NOV2009 Acid Indigestion 1 Sodium bicarbonate
6 002 18NOV2009 Nausea 1 Dopamine
7 003 18NOV2009 Fever 1 Asprin
8 005 17NOV2009 Fever 1 Asprin
This is a lot of code. An important note here is that I have had to rename the matching variables in CM so that they do not overwrite the original values in AE (very important) but use the DROP statment to get rid of these when the dataset ALL1 is created. Note also that I have printed out the variable MATCH so that it is easy to see where the match is.
Yippie, the output is what we have expected AND the we have not used SQL but instead used a datastep.
There are a couple of other ways to do this merge without SQL, notably a variation on the "Loading Unique Dataset into an Array" which I presented first at NESUG in 2008, which can be seen here, but the technique shown above is a shorter method with similar control (however the use of the array will do a many to many merge with the equivilant SQL OUTER JOIN). Personally I prefer the datastep approach given above since it has the most control.
Hope this helps. Have a very happy and safe holiday season. See you all next year.
|