BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
GPatel
Pyrite | Level 9

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;

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

7 REPLIES 7
stat_sas
Ammonite | Level 13

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;

GPatel
Pyrite | Level 9


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

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



art297
Opal | Level 21

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;

GPatel
Pyrite | Level 9

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

Regards....Girish Patel

Aman4SAS
Obsidian | Level 7

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.

art297
Opal | Level 21

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

Loko
Barite | Level 11

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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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