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
??
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;
data mytable2;
ids="&ids";
i = 1;
do until (scan(ids,i) = ' ');
names = scan(ids,i);
output;
i = i + 1;
end;
run;
Thank you for your answer it's working perfectly,
So I guess it's not possible to do this with a proc sql ?
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.
??
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;
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.
..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 ....)
I did not think about this proc sql property. Thank you very much for your help
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.