DATA Step, Macro, Functions and more

Create Macro List

Accepted Solution Solved
Reply
Contributor
Posts: 69
Accepted Solution

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: 7,467

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;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,228

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

Re: Create Macro List


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

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



Solution
‎10-08-2014 09:11 PM
PROC Star
Posts: 7,467

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

Re: Create Macro List

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

Regards....Girish Patel

Super Contributor
Posts: 266

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: 7,467

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

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 294 views
  • 3 likes
  • 5 in conversation