DATA Step, Macro, Functions and more

how to replace into with "symptux"

Reply
Super Contributor
Posts: 345

how to replace into with "symptux"

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

Trusted Advisor
Posts: 1,566

Re: how to replace into with "symptux"

The INTO: is a sql syntax.

 

CALL SYMPUT is a sas data step syntax.

Super User
Posts: 19,815

Re: how to replace into with "symptux"

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

Super Contributor
Posts: 345

Re: how to replace into with "symptux"


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

 

Trusted Advisor
Posts: 1,566

Re: how to replace into with "symptux"

[ Edited ]

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;

Super Contributor
Posts: 345

Re: how to replace into with "symptux"

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

Trusted Advisor
Posts: 1,566

Re: how to replace into with "symptux"

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;
Super Contributor
Posts: 345

Re: how to replace into with "symptux"

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

Trusted Advisor
Posts: 1,566

Re: how to replace into with "symptux"

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 ?

 

Super Contributor
Posts: 345

Re: how to replace into with "symptux"

'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

 

Super User
Posts: 19,815

Re: how to replace into with "symptux"

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?

 


wenling 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

 


 

Super User
Posts: 19,815

Re: how to replace into with "symptux"


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

Ask a Question
Discussion stats
  • 11 replies
  • 173 views
  • 0 likes
  • 3 in conversation