topic Re: Add a new column from a macro variable proc sql in SAS Procedures

Add a new column from a macro variable proc sql

yomsx — Fri, 03 Apr 2015 09:23:04 GMT

Hi,

I have a macro variable ids, containing the names of my subjects under study.

proc sql noprint;

select distinct(names) into :ids separated by " "

from mytable1;

quit;

how could I use my macro variable ids to create a new column containing the names of my subjects in an other table called mytable2 ? I think I'm looking for an equivalent of symget in proc sql.

Thank you very much

Yohann

Re: Add a new column from a macro variable proc sql

Kurt_Bremser — Fri, 03 Apr 2015 09:29:12 GMT

data mytable2;

ids="&ids";

i = 1;

do until (scan(ids,i) = ' ');

names = scan(ids,i);

output;

i = i + 1;

end;

run;

Re: Add a new column from a macro variable proc sql

yomsx — Fri, 03 Apr 2015 09:36:39 GMT

Thank you for your answer it's working perfectly,

So I guess it's not possible to do this with a proc sql ?

Re: Add a new column from a macro variable proc sql

Kurt_Bremser — Fri, 03 Apr 2015 10:30:07 GMT

Maybe someone else could help you out, but I think that is done best in the data step. I can't come up wit a SQL solution.

Re: Add a new column from a macro variable proc sql

Tom — Fri, 03 Apr 2015 10:41:43 GMT

Why not just use the same SQL query to make the new table?

proc sql noprint;

create table new_table as select distinct names

from mytable1;

quit;

Re: Add a new column from a macro variable proc sql

yomsx — Fri, 03 Apr 2015 12:08:01 GMT

Tom,

I chose a very simple (maybe too simple) example to illustrate a question I was wondering about macro variables in proc sql. I was a bit surprised it was very easy to create a macro variable from a given column of a table but that the inverse operation was not.

Re: Add a new column from a macro variable proc sql

Kurt_Bremser — Fri, 03 Apr 2015 12:13:17 GMT

..but that the inverse operation was not.

It is not necessary, since in SQL you can nest a select within a select.

And create a condition like

where x in (select y from ... where ....)

Re: Add a new column from a macro variable proc sql

yomsx — Fri, 03 Apr 2015 12:23:43 GMT

I did not think about this proc sql property. Thank you very much for your help