Solved
Contributor
Posts: 71

# Create Macro List

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;

Accepted Solutions
Solution
‎10-08-2014 09:11 PM
PROC Star
Posts: 8,164

## Re: Create Macro List

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;

All Replies
Posts: 1,270

## Re: Create Macro List

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;

Contributor
Posts: 71

## Re: Create Macro List

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

Solution
‎10-08-2014 09:11 PM
PROC Star
Posts: 8,164

## Re: Create Macro List

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;

Contributor
Posts: 71

## Re: Create Macro List

Regards....Girish Patel

Super Contributor
Posts: 271

## Re: Create Macro List

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.

PROC Star
Posts: 8,164

## Re: Create Macro List

: 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'

Super Contributor
Posts: 319

## Re: Create Macro List

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;

🔒 This topic is solved and locked.