Help using Base SAS procedures

Add a new column from a macro variable proc sql

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Add a new column from a macro variable proc sql

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


Accepted Solutions
Solution
‎04-03-2015 06:41 AM
Super User
Super User
Posts: 6,704

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

??

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;

View solution in original post


All Replies
Super User
Posts: 7,102

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

data mytable2;

ids="&ids";

i = 1;

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

  names = scan(ids,i);

  output;

  i = i + 1;

end;

run;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 10

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

Thank you for your answer it's working perfectly,

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

Super User
Posts: 7,102

Re: Add a new column from a macro variable 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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎04-03-2015 06:41 AM
Super User
Super User
Posts: 6,704

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

??

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;

Occasional Contributor
Posts: 10

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

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.

Super User
Posts: 7,102

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 10

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

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 751 views
  • 6 likes
  • 3 in conversation