proc sql noprint;
select distinct cats("'",compress(patient_id),"'") into :readmi separated by ","
from lib1.have
where numberofvisit gt 1;
quit;
I want to learn more about "CALL SYMPUTX":, so I guess it is a good idea to replace "into" with "call symputx".
Any advice how to do that? Thank you
The INTO: is a sql syntax.
CALL SYMPUT is a sas data step syntax.
In this example, SQL is more appropriate. CALL SYMPUTX() is used for creating single variables, but for a list of values, the SQL INTO is better. A more analogous comparison would be converting the following into a data step solution:
proc sql noprint;
select name into :name1-
from sashelp.class;
quit;
%put &name1.;
%put &name15.;
Data want;
Set sashelp.class;
CALL SYMPUT('name'||right(PUT(1, 3.)), '-');
CALL SYMPUT('name'||right(PUT(1, 15.)), '-');
run;
Is it like that? Would you please correct if it is wrong? Thank you
Your original sql code was:
proc sql noprint;
select distinct cats("'",compress(patient_id),"'") into :readmi separated by ","
from lib1.have
where numberofvisit gt 1;
quit;
%put &readmi;
that creates a macro variable with a list of the names separated by comma.
The equivalent sas data step will be:
data _NULL_;
set lib1.have end=eof;
length readmi $500; /* length to hold the full created list */
retain readmi;
if _N_ = 1 then readmi = quote(patiend_id);
else readmi = catx(',',readmi,quote(patient_id));
if eof then cal symputx('readmi',readmi);
run;
%put &readmi;
Run both codes and compare results in log;
I am sorry. I notice the code is
proc sql noprint;
select distinct cats("'",compress(patient_id),"'") into :readmi separated by ","
from lib1.have
where multivisit = 1; (instead of numerofvisit gt 1)
quit;%put &readmi;
Or should I use another step to change the code, add the variable, numberofvist and use your code? Or is there one step given the code with the variable multivisit instead of numberofvisit
I have missed the WHERE statemnt.
You can enter it in the same step:
data _NULL_;
set lib1.have(where=(multivisit=1)) end=eof;
length readmi $500; /* length to hold the full created list */
retain readmi;
if _N_ = 1 then readmi = quote(patiend_id);
else readmi = catx(',',readmi,quote(patient_id));
if eof then cal symputx('readmi',readmi);
run;%put &readmi;
Thank you. if I need to use two where statements for two variables, should it be:
data _NULL_;
set lib1.have(where=(multivisit=1)) end=eof;
length readmi $500; /* length to hold the full created list */
retain readmi;
if _N_ = 1 then readmi = quote(patiend_id);
else readmi = catx(',',readmi,quote(patient_id));
if eof then cal symputx('readmi',readmi);
data _null_;
set lib1.have(where=(multibirth=1)) end=eof;
length highrisk $500;
retain highrisk;
if _N_ = 1 then highrisk= quote(patiend_id);
else highrisk= catx(',',highrisk,quote(patient_id));
if eof then cal symputx('highrisk',highrisk);
and then to merge these two datasets??
The meaning of DATA _NULL_ is - do not create output dataset .
Each of your two data steps will create a macro variable with a list of patient_id .
So finally you have two macro variables - tobe used as &readmi and &highrisk.
What else do you need or want to do with those lists ?
'23','357','abc','apc11'
SYMBOLGEN: Macro variable MULTivisit resolves to "apc11 ","apc11 ","357 ","apc11 ","apc11 ","357
","357 ","apc11 ","23 ","apc11 ","apc11 ","357 ","apc11 ","357 ","apc11
","239 ","357 ","apc11 ","46 ","46 ","357 ","apc11 ","357 ","apc11
","305 ","apc11 ","apc11 ","357 ","apc11 ","357 ","357 ","apc11 ","357
","apc11 ","apc11 ","371 ","apc11 ","357 ","apc11,"
I compared two datasets generated and they are different. From the log above, you can see, with the macro variable, multivisit, with "into", it generates a list of macro variables, but with "call symputx", it generates duplicates, and you can see, the "," is generated differently
Well yes. The code isn't exact and the point of the exercise is to learn how to make them exact isn't it?
What's different in the SQL that you should have in your data step?
@Bal23 wrote:
'23','357','abc','apc11' SYMBOLGEN: Macro variable MULTivisit resolves to "apc11 ","apc11 ","357 ","apc11 ","apc11 ","357 ","357 ","apc11 ","23 ","apc11 ","apc11 ","357 ","apc11 ","357 ","apc11 ","239 ","357 ","apc11 ","46 ","46 ","357 ","apc11 ","357 ","apc11 ","305 ","apc11 ","apc11 ","357 ","apc11 ","357 ","357 ","apc11 ","357 ","apc11 ","apc11 ","371 ","apc11 ","357 ","apc11,"
I compared two datasets generated and they are different. From the log above, you can see, with the macro variable, multivisit, with "into", it generates a list of macro variables, but with "call symputx", it generates duplicates, and you can see, the "," is generated differently
@Bal23 wrote:
Data want; Set sashelp.class; CALL SYMPUT('name'||right(PUT(1, 3.)), '-'); CALL SYMPUT('name'||right(PUT(1, 15.)), '-'); run;
Is it like that? Would you please correct if it is wrong? Thank you
I shouldn't need to tell you if it's correct or wrong. You can resolve the macro variables and see if they resolve to the same underlying values. But....in this case, it's not correct.
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.