topic Re: how to replace into with "symptux" in SAS Programming

how to replace into with "symptux"

Bal23 — Mon, 13 Nov 2017 00:38:19 GMT

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

Re: how to replace into with "symptux"

Shmuel — Mon, 13 Nov 2017 00:42:01 GMT

The INTO: is a sql syntax.

CALL SYMPUT is a sas data step syntax.

Re: how to replace into with "symptux"

Reeza — Mon, 13 Nov 2017 00:44:25 GMT

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

Re: how to replace into with "symptux"

Bal23 — Mon, 13 Nov 2017 01:28:17 GMT


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

Re: how to replace into with "symptux"

Shmuel — Mon, 13 Nov 2017 04:06:59 GMT

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;

Re: how to replace into with "symptux"

Bal23 — Mon, 13 Nov 2017 12:39:11 GMT

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

Re: how to replace into with "symptux"

Shmuel — Mon, 13 Nov 2017 15:10:22 GMT

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;

Re: how to replace into with "symptux"

Bal23 — Mon, 13 Nov 2017 15:36:16 GMT

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

Re: how to replace into with "symptux"

Shmuel — Mon, 13 Nov 2017 15:52:03 GMT

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 ?

Re: how to replace into with "symptux"

Reeza — Mon, 13 Nov 2017 17:50:11 GMT

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

Re: how to replace into with "symptux"

Bal23 — Thu, 16 Nov 2017 15:53:54 GMT

'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

Re: how to replace into with "symptux"

Reeza — Thu, 16 Nov 2017 16:03:37 GMT

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