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
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.
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.
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.
Can't believe I forgot the quotes in the list....bah!
thanks all
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.