I am creating multiple tables in one program (each table requiring different subsets, some have one parameter, others have multiple). How do I add more than one subset by using keyword parameter?
current code (I want to use "and astdy >=28" in a keyword parameter.:
%macro dotable( popfl=);
proc sort data = adam.adae out = adae;
by usubjid;
where &popfl = 'Y' and astdy >=28;
run;
%dotable( popfl=saffl );
So you require the keyword parameters? - positional parameters are not an option? If you can use positional parameters then this should be possible:
%macro dotable( popfl, positional_parameter);
proc sort data = adam.adae out = adae;
by usubjid;
where &popfl = 'Y' and &positional_parameter;
run;
%MEND;
%dotable(saffl , astdy >=28);
@PhilC wrote:
So you require the keyword parameters? - positional parameters are not an option? If you can use positional parameters then this should be possible:
%macro dotable( popfl, positional_parameter); proc sort data = adam.adae out = adae; by usubjid; where &popfl = 'Y' and &positional_parameter; run; %MEND;
%dotable(saffl , astdy >=28);
Which generates an error if the positional parameter is not provided or is intentionally blank, same as with a missing keyword parameter.
Unless you are going to attempt to pass really ugly parameters you may be able to get away with placing the parameter value in a %str( ) when using a character like =.
%macro dummy(parm = , parm2=); data work.junk; set sashelp.class; where %if %length(&parm.) > 0 %then %do; &parm. /* if the second parameter is present then add an "and" before the parameter string */ %if %length(&parm2.) >0 %then %do; and %end; %end; %if %length(&parm2.) > 0 %then %do; &parm2. %end; /* the next ; ends the where statement*/ ; run; %mend; %dummy (parm=%str(sex='F'), parm2=%str(age = 14));
The above has logic to conditionally have AND added when you have two parameters. If only one parameter is passed then only one is used. Note this can get extremely complex quickly.
The Where ; which would result with two blank keyword parameters will not subset the data at all.
I see, I think. I don't think you want to go down to road where you put quotes into a macro so that you can parse out a boolean expression with an equal sign while using keyword parameters. Maybe you would, honestly I wouldn't care to help you do that, This is what I've done doing something similar.
%macro dotable(conditional);
proc sort data = adam.adae out = adae;
by usubjid;
where &conditional;
run;
%MEND;
*1; %dotable( &popfl = 'Y' and astdy >=28 );
*2; %dotable( &popfl = 'Y' );
*3; %dotable( 1 );
So since &popfl
will be provided, I assume there would be no problem doing *1;
and *2;
. The full table of data would be called using case *3;
.
With this we may use equal signs in the macro, but, beaware now, we can not use this macro function with conditionals that use commas!
I don't understand what you want. Are you asking how to modify the macro to accept more than one parameter? Or are you asking how to allow more than one value to passed for an existing parameter?
Note that whether the parameter is defined to allow passing values by position does not prevent you from passing the values using the parameter names in the macro call.
@HitmonTran wrote:
eg. Table 1 has subset of males
eg. Table 2 has subset of males and age > 18
eg. Table 3 has subset of males and age > 18 and location= california.
all in one program. I want to create a macro for all possible criterias
By "Table" do you mean data set or displayed table in a report?
If a dataset please provide examples of how you are going to actually use multiple data sets.
Multiple data sets with probability approaching unity are likely not be needed unless you have to reduce truly humongous data (think 100GB or bigger) to subsets to avoid resource limits for specific operations.
@HitmonTran wrote:
eg. Table 1 has subset of males
eg. Table 2 has subset of males and age > 18
eg. Table 3 has subset of males and age > 18 and location= california.
all in one program. I want to create a macro for all possible criterias
Sounds simple to do with a single parameter. But it will have a different meaning than the parameter you have designed in the original code:
%macro dotable( popfl=);
proc sort data = adam.adae out = adae;
by usubjid;
where &popfl = 'Y' and astdy >=28;
run;
%mend dotable;
%dotable( popfl=saffl )
In that macro the meaning of POPFL is the NAME of the dataset variable to test.
So you can either change the macro to use a different parameter that means what where clause to use:
%macro dotable( where=);
proc sort data = adam.adae out = adae;
by usubjid;
where &where;
run;
%mend dotable;
%dotable( where=saffl= 'Y' and astdy >=28 )
Or add another parameter that means what ADDITIONAL where clause to run. Here you might take advantage of defining the parameter as named to include a default value.
%macro dotable( popfl=, where= 1=1 );
proc sort data = adam.adae out = adae;
by usubjid;
where &popfl = 'Y' ;
where also &where;
run;
%mend dotable;
%dotable( popfl=saffl , where=astdy >=28)
@HitmonTran wrote:
eg. Table 1 has subset of males
eg. Table 2 has subset of males and age > 18
eg. Table 3 has subset of males and age > 18 and location= california.
all in one program. I want to create a macro for all possible criterias
I'll show you mine. This is an example of creating additional variables and then using them instead of creating lots of subsets.
You should have the SASHELP.Class data set available to run the data.
data work.class; set sashelp.class; IsBoy = (sex='M'); IsBoy_14 = (sex='M' and age ge 14); IsBoy_14_tall = (isboy_14 and height ge 60); run; /* these assignments create variables that are valued 1 for true and 0 for false. Following are sum examples of using these */ Title "Details of Tall boys over 14"; Proc print data=work.class noobs; where isboy_14_tall; run;title; title "Summary statistics for height and weight, Boys over 14"; Proc means data=work.class; where IsBoy_14; var height weight; run; title; title 'Age count of boys'; proc freq data=work.class; where isboy; tables age; run; title; title 'Age count of girls'; Proc freq data=work.class; where not (isboy); tables age; run; title;
Note that the last one uses knowledge of what negating a boolean expression or value means. With that understanding we don't need any extra "IsGirl" variable, though negating one of the compound versions has a bit trickier meaning.
I can use that same data set to run regressions, graphs or what ever. And I don't have to remember (or spell) multiple dataset names.
Another issue with many data sets is length of data set name. Currently SAS is limited to 32 characters for the name of a data set. When you say "all combinations" how many variables do we need to make the combinations? Since your example included a statename that implies there may be 50+ sets minimum with some bit of state value in name. Age? how many levels? If you have 5 age groups then we are now potentially looking at 5*50 (or more if using territories and protectorates) = 250 set names to remember. Add in Sex you are looking at 2*250=500. Actually could be quite a bit more if Male, Female and not subsetted on sex is 3*250=750 or more data sets. I really don't want to look at esoteric combinations of letters and try to remember what each of these sets if for.
Your turn. Use the SASHELP.Class data set to create 3 of your subset data sets. The write proc code that uses them for the purpose(s) you intend.
Why do you need multiple subsets?
It is often very much easier to create flag variables that indicate which group(s) a record belongs to and then use a Where to select the needed records. That way you don't have to chase down lots of potential spelling issues with data set names and re-coded or added variables are always in the data when needed. If you split the data and then need a new variable in multiple sets then you need to go back and recreate all of the subsets.
The definitions of the group variables would (or at least should) all be in one data step.
If the group flags are coded as numeric 1/0, with 1 indicating membership, it is easy to use the variable in an IF or WHERE clause as 1/0 are treated as True/False by SAS. So you could use something like
Proc print data=have;
where groupsaffl_28;
run;
or other reporting or analysis.
It then becomes very easy to find the overlaps between sets:
data temp;
set have;
where groupsaffl_28 and grouppdq;
run;
Otherwise you would be having to 1) identify all the data sets of interest and 2) do some sort of join/merge to identify the common elements.
Or if you want to Exclude another group: Where groupsaffle_28 and Not grouppdq;
So, reconsider NEED of multiple data sets.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: