Given the data set "LKUP" as shown below, how one can create a Macro list with following business logic.
If rptA = Y then generate a string of all car where rptA=Y (e.g. %LET LIST_RPT_A=%NRBQUOTE([Ford].[GM].[Toyota]);
If rptB = Y then generate a string of all car where rptB=Y (e.g. %LET LIST_RPT_B=%NRBQUOTE([Ford].[BMW]);
Data LKUP ;
infile cards missover;
input Car $ rptA $ rptB $;
Cards;
Ford Y Y
GM Y
Toyota Y
BMW . Y
;
Run;
Hard coded......
DATA B;
Set LKUP;
IF RPTA EQ UPCASE('Y') THEN DO;
%LET LIST_RPT_A=%NRBQUOTE([Ford].[GM].[Toyota]);
END;
IF RPTB EQ UPCASE('Y') THEN DO;
%LET LIST_RPT_B=%NRBQUOTE([Ford].[BMW]);
End;
Run;
That's easy to fix:
proc sql;
select catt('[',car,']') into :LIST_RPT_A separated by ' '
from lkup where RPTA EQ UPCASE('Y');
select catt('[',car,']') into :LIST_RPT_B separated by ' '
from lkup where RPTB EQ UPCASE('Y');
quit;
Try this.
proc sql;
select car into :LIST_RPT_A separated by ' '
from lkup where RPTA EQ UPCASE('Y');
select car into :LIST_RPT_B separated by ' '
from lkup where RPTB EQ UPCASE('Y');
quit;
%put &LIST_RPT_A;
%put &LIST_RPT_B;
Thanks for your answer, but it doesn't create desired string.
String should look like : " ([Ford].[GM].[Toyota]) " or " ([Ford].[BMW]) "
That's easy to fix:
proc sql;
select catt('[',car,']') into :LIST_RPT_A separated by ' '
from lkup where RPTA EQ UPCASE('Y');
select catt('[',car,']') into :LIST_RPT_B separated by ' '
from lkup where RPTB EQ UPCASE('Y');
quit;
Thanks Mr. Arthur for your prompt answer. I made it to work. Appreciate your time and interest.
Regards....Girish Patel
Sir, I am confuse,
why use upcase to "Y" , its user given in " where RPTB EQ UPCASE('Y'); "
shoudnt it be like : where upcase(RPTB) EQ "Y" ;
apology in advance for my confusion.
Hello,
Using data step:
Data have ;
infile cards missover;
input Car $ rptA $ rptB $;
Cards;
Ford Y .
GM Y Y
Toyota Y .
BMW . Y
;
Run;
%let LIST_RPT_A=;
%let LIST_RPT_B=;
data _null_;
set have;
IF RPTA EQ UPCASE('Y') THEN
do;
if symget("LIST_RPT_A")="" then call symputx("LIST_RPT_A", cats("[",Car,"]"));
else call symputx("LIST_RPT_A", cats(symget("LIST_RPT_A"),".[",Car,"]"));
end;
IF RPTB EQ UPCASE('Y') THEN
do;
if symget("LIST_RPT_B")="" then call symputx("LIST_RPT_B", cats("[",Car,"]"));
else call symputx("LIST_RPT_B", cats(symget("LIST_RPT_B"),".[",Car,"]"));
end;
Run;
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 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.