Hi,
I'm trying to loop through a list of values inputted througha prompt, then use those values to create a dataset. Here's the working code:
data batters_1;
set batter_lookup;
where year_id = &season_year and (
bat_id = "&player_id_lookup1" or bat_id = "&player_id_lookup2" or
bat_id = "&player_id_lookup3" or bat_id = "&player_id_lookup4" or
bat_id = "&player_id_lookup5" or bat_id = "&player_id_lookup6" or
bat_id = "&player_id_lookup7" or bat_id = "&player_id_lookup8" or
bat_id = "&player_id_lookup9");
run;
But I'd like to be able to change the number of players to how many have inputted in order to expand or contract the list. Here's what I've tried to do:
%MACRO batter_class;
%do i=1 %to &Player_ID_Lookup_count;
data batters_2;
set batter_lookup;
where year_id = &season_year and bat_id = "&player_id_lookup&i";
run;
%end;
%MEND batter_class;
The macro will run, but the dataset is empty. Here are the logs:
From the working program:
;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='finished player lookup with Prompt';
4 %LET _CLIENTPROCESSFLOWNAME='Final Stuff';
5 %LET _CLIENTPROJECTPATH='C:\Users\jblon\Documents\My SAS Files\baseball_8.egp';
6 %LET _CLIENTPROJECTPATHHOST='DESKTOP-BLJ64FN';
7 %LET _CLIENTPROJECTNAME='baseball_8.egp';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10 %LET Player_ID_Lookup3 = rodra001;
11 %LET Player_ID_Lookup2 = headc001;
12 %LET Player_ID_Lookup1 = ellsj001;
13 %LET Player_ID_Lookup0 = 9;
14 %LET Player_ID_Lookup7 = birdg001;
15 %LET Player_ID_Lookup6 = younc004;
16 %LET Player_ID_Lookup = ellsj001;
17 %LET Player_ID_Lookup4 = beltc001;
18 %LET Player_ID_Lookup5 = mccab002;
19 %LET Player_ID_Lookup9 = gregd001;
20 %LET Player_ID_Lookup_count = 9;
21 %LET Player_ID_Lookup8 = refsr001;
22 %LET season_year = 2015;
23
24 ODS _ALL_ CLOSE;
25 OPTIONS DEV=ACTIVEX;
26 FILENAME EGSR TEMP;
27 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
28 STYLE=HtmlBlue
29 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
30 NOGTITLE
31 NOGFOOTNOTE
32 GPATH=&sasworklocation
33 ENCODING=UTF8
34 options(rolap="on")
35 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
36
37 data batters_1;
38 set batter_lookup;
39 where year_id = &season_year and (
40 bat_id = "&player_id_lookup1" or bat_id = "&player_id_lookup2" or
41 bat_id = "&player_id_lookup3" or bat_id = "&player_id_lookup4" or
42 bat_id = "&player_id_lookup5" or bat_id = "&player_id_lookup6" or
43 bat_id = "&player_id_lookup7" or bat_id = "&player_id_lookup8" or
44 bat_id = "&player_id_lookup9");
45 run;
NOTE: There were 9 observations read from the data set WORK.BATTER_LOOKUP.
WHERE (year_id=2015) and bat_id in ('beltc001', 'birdg001', 'ellsj001', 'gregd001', 'headc001', 'mccab002', 'refsr001',
'rodra001', 'younc004');
NOTE: The data set WORK.BATTERS_1 has 9 observations and 9 variables.
NOTE: DATA statement used (Total process time):
real time 2.22 seconds
cpu time 0.23 seconds
46
Then here's the macro code's log:
OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='finished player lookup with Prompt';
4 %LET _CLIENTPROCESSFLOWNAME='Final Stuff';
5 %LET _CLIENTPROJECTPATH='C:\Users\jblon\Documents\My SAS Files\baseball_8.egp';
6 %LET _CLIENTPROJECTPATHHOST='DESKTOP-BLJ64FN';
7 %LET _CLIENTPROJECTNAME='baseball_8.egp';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10 %LET Player_ID_Lookup3 = rodra001;
11 %LET Player_ID_Lookup2 = headc001;
12 %LET Player_ID_Lookup1 = ellsj001;
13 %LET Player_ID_Lookup0 = 9;
14 %LET Player_ID_Lookup7 = birdg001;
15 %LET Player_ID_Lookup6 = younc004;
16 %LET Player_ID_Lookup = ellsj001;
17 %LET Player_ID_Lookup4 = beltc001;
18 %LET Player_ID_Lookup5 = mccab002;
19 %LET Player_ID_Lookup9 = gregd001;
20 %LET Player_ID_Lookup_count = 9;
21 %LET Player_ID_Lookup8 = refsr001;
22 %LET season_year = 2015;
23
24 ODS _ALL_ CLOSE;
25 OPTIONS DEV=ACTIVEX;
26 FILENAME EGSR TEMP;
27 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
28 STYLE=HtmlBlue
29 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
30 NOGTITLE
31 NOGFOOTNOTE
32 GPATH=&sasworklocation
33 ENCODING=UTF8
34 options(rolap="on")
35 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
36
37 %batter_class;
NOTE: There were 0 observations read from the data set WORK.BATTER_LOOKUP.
WHERE (year_id=2015) and (bat_id='ellsj0011');
NOTE: The data set WORK.BATTERS_2 has 0 observations and 9 variables.
NOTE: DATA statement used (Total process time):
real time 0.16 seconds
cpu time 0.10 seconds
NOTE: There were 0 observations read from the data set WORK.BATTER_LOOKUP.
WHERE (year_id=2015) and (bat_id='ellsj0012');
NOTE: The data set WORK.BATTERS_2 has 0 observations and 9 variables.
NOTE: DATA statement used (Total process time):
real time 0.14 seconds
cpu time 0.12 seconds
NOTE: There were 0 observations read from the data set WORK.BATTER_LOOKUP.
2 The SAS System 20:35 Friday, April 28, 2017
WHERE (year_id=2015) and (bat_id='ellsj0013');
NOTE: The data set WORK.BATTERS_2 has 0 observations and 9 variables.
NOTE: DATA statement used (Total process time):
real time 0.14 seconds
cpu time 0.12 seconds
NOTE: There were 0 observations read from the data set WORK.BATTER_LOOKUP.
WHERE (year_id=2015) and (bat_id='ellsj0014');
NOTE: The data set WORK.BATTERS_2 has 0 observations and 9 variables.
NOTE: DATA statement used (Total process time):
real time 0.13 seconds
cpu time 0.09 seconds
NOTE: There were 0 observations read from the data set WORK.BATTER_LOOKUP.
WHERE (year_id=2015) and (bat_id='ellsj0015');
NOTE: The data set WORK.BATTERS_2 has 0 observations and 9 variables.
NOTE: DATA statement used (Total process time):
real time 0.13 seconds
cpu time 0.10 seconds
NOTE: There were 0 observations read from the data set WORK.BATTER_LOOKUP.
WHERE (year_id=2015) and (bat_id='ellsj0016');
NOTE: The data set WORK.BATTERS_2 has 0 observations and 9 variables.
NOTE: DATA statement used (Total process time):
real time 0.19 seconds
cpu time 0.09 seconds
NOTE: There were 0 observations read from the data set WORK.BATTER_LOOKUP.
WHERE (year_id=2015) and (bat_id='ellsj0017');
NOTE: The data set WORK.BATTERS_2 has 0 observations and 9 variables.
NOTE: DATA statement used (Total process time):
real time 0.13 seconds
cpu time 0.09 seconds
NOTE: There were 0 observations read from the data set WORK.BATTER_LOOKUP.
WHERE (year_id=2015) and (bat_id='ellsj0018');
NOTE: The data set WORK.BATTERS_2 has 0 observations and 9 variables.
NOTE: DATA statement used (Total process time):
real time 0.14 seconds
cpu time 0.10 seconds
NOTE: There were 0 observations read from the data set WORK.BATTER_LOOKUP.
WHERE (year_id=2015) and (bat_id='ellsj0019');
NOTE: The data set WORK.BATTERS_2 has 0 observations and 9 variables.
NOTE: DATA statement used (Total process time):
real time 0.16 seconds
3 The SAS System 20:35 Friday, April 28, 2017
cpu time 0.11 seconds
Is there a better way to acomplish this, or am I missing something in the macro code?
Thank you.
You could add a series of IF statements to create a new variable that has the value of the macro varible I.
Is that what you mean?
%do i=1 %to &Player_ID_Lookup_count;
if bat_id="&&player_id_lookup&i" then selection_order = &i ;
%end;
Your test macro variables in the two use cases are different, so we can't actually be sure that there is valid data.
That being said, are you trying to create multiple datasets or a single data set?
Can you use IN instead of multiple OR conditions:
if bad_id in (list of macro variables goes here);
If you want BATTERS_2 data step to look like the BATTERS_1 data step then your %DO loop is in the wrong place.
You need another & to get SAS to evaluate the calculated macro variable name.
You also need to watch out for single selection where SAS will not make the numeric suffixed macro variables.
NOTE: SAS doesn't need commas between the terms listed in the IN () list.
%MACRO batter_class;
%local i ;
%let player_id_lookup1=&player_id_lookup;
data batters_2;
set batter_lookup;
where year_id = &season_year
and bat_id in (
%do i=1 %to &Player_ID_Lookup_count;
"&&player_id_lookup&i"
%end;
);
run;
%MEND batter_class;
Hi Tom,
That worked perfectly. Along with that, I have one more question. Currently SAS is sorting the final dataset in descending order. Is there a way to have SAS keep them in the same order, and also assign a number from 1-9 to them, in a different column?
For example, heres a list of the id's being looked up:
So in a new column, have 1, then the player_1, then 2 and player_2, instead of sorting them by descending order?
I really appreciate the help.
You could add a series of IF statements to create a new variable that has the value of the macro varible I.
Is that what you mean?
%do i=1 %to &Player_ID_Lookup_count;
if bat_id="&&player_id_lookup&i" then selection_order = &i ;
%end;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.