SQL and Select from Macro Variable List

[BCNAV]

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

[Reeza]

Not valid SAS sytnaxt.

I'm guessing you want:

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

[PaigeMiller]

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.

---

[Reeza]

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

Change = to IN as well.

[ballardw · Accepted solution]

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.

[BCNAV]

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

thanks all

[ballardw]

---

@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.