Hello
I am using proc sql with into statement to create SAS Macro Varaible.
In this example I create a Macro Varaible called nameP that should recieve value "Peter","Paul"
What is the correct way to get it?
In the code that I wrote I get value Peter,Paul and then there is an error
******INTO Statement in PROC SQL to Create Macro Variables******;
data RawData;
length name $10;
input name $;
datalines;
Peter
John
Paul
David
;
run;
/*Solution with error*/
proc sql noprint;
select name
into :namesP separated by ','
from RawData
where substr(name,1,1) = 'P'
;
quit;
%put &namesP;/*Peter,Paul*/
data Outcome;
set RawData;
where name IN (&namesP.);
run;
/*Correct solution*/
%let names="Peter","Paul";
data Outcome;
set RawData;
where name IN (&names.);
run;
If you need quotes, you have to set them:
data RawData;
length name $10;
input name $;
datalines;
Peter
John
Paul
David
;
run;
proc sql noprint;
select quote(trim(name))
into :namesP separated by ','
from RawData
where substr(name,1,1) = 'P'
;
quit;
%put &namesP;
Log excerpt:
46 %put &namesP; "Peter","Paul"
If you need quotes, you have to set them:
data RawData;
length name $10;
input name $;
datalines;
Peter
John
Paul
David
;
run;
proc sql noprint;
select quote(trim(name))
into :namesP separated by ','
from RawData
where substr(name,1,1) = 'P'
;
quit;
%put &namesP;
Log excerpt:
46 %put &namesP; "Peter","Paul"
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.