BookmarkSubscribeRSS Feed
Bal23
Lapis Lazuli | Level 10
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

11 REPLIES 11
Shmuel
Garnet | Level 18

The INTO: is a sql syntax.

 

CALL SYMPUT is a sas data step syntax.

Reeza
Super User

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

Bal23
Lapis Lazuli | Level 10

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

 

Shmuel
Garnet | Level 18

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;

Bal23
Lapis Lazuli | Level 10

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

Shmuel
Garnet | Level 18

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;
Bal23
Lapis Lazuli | Level 10

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??

Shmuel
Garnet | Level 18

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 ?

 

Bal23
Lapis Lazuli | Level 10
'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

 

Reeza
Super User

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

 


 

Reeza
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1376 views
  • 0 likes
  • 3 in conversation