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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.