CPWHERESEL - Alternate subset macro

[Greg Wagner]

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:

%cpwheresel;

proc print data=pviews.enrollment&wheresel;
run;

Macro %CPWHERESEL;

/****************************************************************
*
* 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;

[jproos]

How about handling either numeric or character vars?

Greg,

We use this in place of %cpsubset. Is there any chance this could be revised to handle either character or numeric fields?

Thanks.

[Greg Wagner]

Hi Jason,

I will see if I can look at this sometime this week.

Greg-

[Greg Wagner]

Macro updated to support numeric variables

The CPWHERESEL macro has been updated to support the use of numeric variables. It will check if the variable is formatted. If so, it will check if an informat has been sepcified attempt to use that informat to decode the selected value. Otherwise, it will use the values selected in the listbox selection.