topic Re: Create Macro List in SAS Programming

Create Macro List

GPatel — Wed, 08 Oct 2014 23:38:12 GMT

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;

Re: Create Macro List

stat_sas — Thu, 09 Oct 2014 00:29:44 GMT

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;

Re: Create Macro List

GPatel — Thu, 09 Oct 2014 00:39:24 GMT

Thanks for your answer, but it doesn't create desired string.

String should look like : " ([Ford].[GM].[Toyota]) " or " ([Ford].[BMW]) "

Re: Create Macro List

art297 — Thu, 09 Oct 2014 01:11:59 GMT

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;

Re: Create Macro List

GPatel — Fri, 10 Oct 2014 10:22:32 GMT

Thanks Mr. Arthur for your prompt answer. I made it to work. Appreciate your time and interest.

Regards....Girish Patel

Re: Create Macro List

Aman4SAS — Fri, 10 Oct 2014 10:43:23 GMT

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.

Re: Create Macro List

Loko — Fri, 10 Oct 2014 11:08:03 GMT

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;

Re: Create Macro List

art297 — Fri, 10 Oct 2014 12:48:05 GMT

: The only thing I added to the code suggested by stat@sas was the catt function putting brackets before and after the variable name.. Yes, the upcase('Y') is unnecessary and would have been more effective if it had been:

upcase(RPTB) eq 'Y'