CPWHERESEL - Alternate Subset Macro
The following macro CPWHERESEL works in conjunction with the report requester module and a custom report. It will create a where clause into a macro variable (wheresel) which can then be used as a SAS data set option to directly to subset data for a report. This is more efficient then using the CPSUBSET macro which must process and subset the data set passed to it, running a data step for each selection.
CPHWERESEL does not process any data, but rather returns a where statement fully constructed as a dataset option statement containing each select made or a blank if no selections are made.
Example: (where=(INVESTIGATORNUMBER in( "501" ) and PATIENTNUMBER in( "101" , "102" )))
Macro now supports use of Numeric variables.
Please note that this code has not been formally tested.
Example use of Macro %CPWHERESEL;
Suppose we want to create a report of enrollment data, the macro could be used as follows:
Macro %CPWHERESEL;
The following macro CPWHERESEL works in conjunction with the report requester module and a custom report. It will create a where clause into a macro variable (wheresel) which can then be used as a SAS data set option to directly to subset data for a report. This is more efficient then using the CPSUBSET macro which must process and subset the data set passed to it, running a data step for each selection.
CPHWERESEL does not process any data, but rather returns a where statement fully constructed as a dataset option statement containing each select made or a blank if no selections are made.
Example: (where=(INVESTIGATORNUMBER in( "501" ) and PATIENTNUMBER in( "101" , "102" )))
Macro now supports use of Numeric variables.
Please note that this code has not been formally tested.
Example use of Macro %CPWHERESEL;
Suppose we want to create a report of enrollment data, the macro could be used as follows:
Code:
%cpwheresel; proc print data=pviews.enrollment&wheresel; run;
Code:
/**************************************************************** * * CPWHERESEL Macro * * By: G. Wagner * Date: 08/07/2008 * * Purpose: Dynamically creates a where clause into a macro variable * from the report reqeuster selections and associated * variable. * Must be used in report code associated to a * report template in CPDM. * * 10/21/2009 - Macro updated to support numeric variables. * if numeric variable is formatted, then * macro will check and use an infomrat to decode. * if no infomrat is specified, then best32. will * be used. * * Output variable: global macro variable WHERESEL * * * Example using where variable: * * proc print data=pviews.tracking&wheresel; * run; * ****************************************************************/ %macro cpwheresel; ** declare global macro variable wheresel **; %global wheresel; ** Make sure where macro variable is not already set **; %let wheresel=; data _null_; length wherecls temp $5000; ** Set flag for and condition **; ckand = 0; ** loop thorugh each possible selction box and check if any items are selected **; %do c = 1 %to 10; %if %eval(&&sel&c > 0) %then %do; %let dsid = %sysfunc(open(&&sel&c.dataset)); %let varnum = %sysfunc(varnum(&dsid,&&sel&c.variable)); %let vartyp = %sysfunc(vartype(&dsid,&varnum)); /* variable type */ %let varfmt = %sysfunc(varfmt(&dsid,&varnum)); /* variable format */ %let varinfmt = %sysfunc(varinfmt(&dsid,&varnum)); /* variable informat */ %let rc = %sysfunc(close(&dsid)); if ckand=1 then wherecls= trim(left(wherecls)) || ' and '; wherecls=trim(left(wherecls)) || " &&sel&c.variable in("; temp=''; ** add each selected item to the temporary variable **; %do ic = 1 %to &&sel&c; %if "&vartyp" = "C" %then %do; temp = left(trim(temp)) || ", ""&&sel&c._&ic"""; %end; %else %do; %if %length(&varfmt) > 0 %then %do; %if %length(&varinfmt) > 0 %then %do; temp = left(trim(temp)) || ", "||compress(input("&&sel&c._&ic", &varinfmt)); %end; %else %do; temp = left(trim(temp)) || ", "||compress(input("&&sel&c._&ic",best32.)); %end; %end; %else %do; temp = left(trim(temp)) || ", "||compress(&&sel&c._&ic); %end; %end; %end; temp=trim(left(temp)); temp = substr(temp,2); wherecls=trim(left(wherecls))|| " " || trim(left(temp)) || " ) "; ckand=1; %end; %end; ** build complete where clause for use as dataset option **; if wherecls > '' then wherecls="(where=("||trim(left(wherecls))||"))"; ** put into macrovariable whereselection **; call symput('wheresel',trim(left(wherecls))); run; ** Write value to log window **; %put Custom Where Clause= &wheresel; %mend cpwheresel;
Comment