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