SAS EG: Filtering a dataset via prompt manager variable

Nobody who reads this blog is likely a SAS programmer. However, on the off chance this would be useful to someone who finds it via Google, I wanted to post.

Currently, I am working on a project in SAS Enterprise Guide where I needed to allow the user to provide variable value(s) via prompt manager and then filter the resulting dataset based on the user’s input. After rubbing a few brain cells together, created a macro that does the trick. It works by embedding a loop inside of a WHERE statement in SQL. If the user selects one value the loop ends and returns that value into the WHERE statement. However, if multiple values are selected, the loop iterates until all values have been returned.

There’s probably a better way, but at least this works. Here’s a quick outline of the variables.

PRODUCTCATEGORY: This is the name of the prompt variable I setup. Replace it with your variable of choice.
IN: Input dataset
OUT: Output dataset
FIELD: Variable in the dataset on which the filtering is performed.


%MACRO PROCESS(IN,OUT,FIELD);
PROC SQL; CREATE TABLE &OUT. AS SELECT * FROM &IN.
WHERE &FIELD. IN (
%IF &PRODUCTCATEGORY_COUNT = 1 %THEN
"&PRODUCTCATEGORY";
%ELSE %DO I=1 %TO &PRODUCTCATEGORY_COUNT;
"&&PRODUCTCATEGORY&I"
%END;
);
QUIT;
%MEND;