Add a new column from a macro variable proc sql

[yomsx]

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

[Kurt_Bremser]

data mytable2;

ids="&ids";

i = 1;

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

  names = scan(ids,i);

  output;

  i = i + 1;

end;

run;

[yomsx]

Thank you for your answer it's working perfectly,

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

[Kurt_Bremser]

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.

[Tom · Accepted solution]

??

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;

[yomsx]

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.

[Kurt_Bremser]

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

[yomsx]

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