HOW TO SELECT MULTIPLE VALUES USING WHERE IN CONDITION WITHIN MACRO USING PROC SQL?

atiedt — Wed, 12 Sep 2018 20:29:11 GMT

Hello everyone,

I've written the following macro to use proc sql to perfom a left join on two tables according to certain conditions. The macro works when the where clause references one variable value ('TYPE'), but not on several variable values. Anyone know how can I adjust this so that I can perform this join for several possible variable values using the conditional "where" "in"?

I'd appreciate some advice on this issue!

%MACRO JOINSUBSET(LIB=, DSN=, MAINFORM=, SUBFORM=, DSTYPE=, TYPE=)/MINOPERATOR;

%IF &TYPE IN Accepted Denied Pending Revoked %THEN %DO;

PROC SQL;
CREATE TABLE &LIB..SUM_&MAINFORM._&SUBFORM._&DSTYPE AS
SELECT a.ACT_YEAR, a.ACT_MONTH,
COUNT(DISTINCT a.RECEIPT_NUMBER) AS COUNT
FROM &LIB..&DSN.&MAINFORM a LEFT JOIN
&LIB..&DSN.&SUBFORM b
ON a.IDENTIFIER=b.IDENTIFIER
WHERE a.CURRENT_STATUS IN (SELECT "&TYPE" FROM &LIB..&DSN.&MAINFORM )
AND b.DATA_TYPE="Accept"
GROUP BY a.ACT_YEAR, a.ACT_MONTH;
QUIT;

%END;

%MEND JOINSUBSET;

%JOINSUBSET(LIB=SASUSER, DSN=DATASET_, MAINFORM=S23, SUBFORM=X54, DSTYPE=DENIED, TYPE=Denied Revoked)

Re: HOW TO SELECT MULTIPLE VALUES USING WHERE IN CONDITION WITHIN MACRO USING PROC SQL?

HB — Thu, 13 Sep 2018 21:17:36 GMT

I would want to know if the SQL correctly runs by itself outside the macro and I would want to put single quotes on Accept.

topic Re: HOW TO SELECT MULTIPLE VALUES USING WHERE IN CONDITION WITHIN MACRO USING PROC SQL? in SAS Programming

HOW TO SELECT MULTIPLE VALUES USING WHERE IN CONDITION WITHIN MACRO USING PROC SQL?

Re: HOW TO SELECT MULTIPLE VALUES USING WHERE IN CONDITION WITHIN MACRO USING PROC SQL?