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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.