topic SQL and Select from Macro Variable List in SAS Programming

SQL and Select from Macro Variable List

BCNAV — Fri, 02 Aug 2019 15:22:37 GMT

I have:

%let ac_grp = "N02299 N99761 N79986 N99121 N01899 N09955";

PROC SQL;
CREATE TABLE EGTASK.TESTER AS
SELECT *
FROM EGTASK.ENR_CURR_V4 (where= (client_id = (&ac_grp)));
QUIT;

This ends up not picking anything up from the original table. The client_ids are in the ac_grp macro variable. Probably something easy that I'm missing. Thanks

Re: SQL and Select from Macro Variable List

Reeza — Fri, 02 Aug 2019 15:24:29 GMT

Not valid SAS sytnaxt.

I'm guessing you want:

%let ac_grp = "N02299", N99761", "N79986" ....

Re: SQL and Select from Macro Variable List

Reeza — Fri, 02 Aug 2019 15:24:56 GMT

(where= (client_id IN (&ac_grp)));

Change = to IN as well.

Re: SQL and Select from Macro Variable List

PaigeMiller — Fri, 02 Aug 2019 15:29:27 GMT

Adding to @Reeza's comment

These types of macro errors can be avoided by first creating code that works on just a small number of cases (such as just these two N02299 N99761 hard coded) WITHOUT MACROs and WITHOUT MACRO VARIABLEs. Once you have code that works without any macro code, then converting it to macros will have a higher probability of working.

Then if you hard coded these first in your code, you would see that

PROC SQL;
     CREATE TABLE EGTASK.TESTER AS
     SELECT *
     FROM EGTASK.ENR_CURR_V4 (where= (client_id = ("N02299 N99761")));
QUIT;

doesn't work. If you get the above to work, your macro will probably work.

Re: SQL and Select from Macro Variable List

ballardw — Fri, 02 Aug 2019 15:35:43 GMT

Try

%let ac_grp = "N02299" "N99761" "N79986" "N99121" "N01899" "N09955";

PROC SQL;
     CREATE TABLE EGTASK.TESTER AS
     SELECT *
     FROM EGTASK.ENR_CURR_V4 (where= (client_id in (&ac_grp)));
QUIT;

It may also help to consider a version of the code that worked without any macro elements at all.

You were comparing individual values of the client_id to an aggregated list (one long single value) not looking for it match any of the components of the list.

Re: SQL and Select from Macro Variable List

BCNAV — Fri, 02 Aug 2019 15:42:31 GMT

Can't believe I forgot the quotes in the list....bah!

thanks all

Re: SQL and Select from Macro Variable List

ballardw — Fri, 02 Aug 2019 15:49:13 GMT

@BCNAV wrote:

Can't believe I forgot the quotes in the list....bah!

thanks all

The use of = instead of IN would have created an error with the properly quoted list.