SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
atiedt
Fluorite | Level 6

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)

1 REPLY 1
HB
Barite | Level 11 HB
Barite | Level 11

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. 

sas-innovate-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 2091 views
  • 0 likes
  • 2 in conversation