Macro with Prompt Values and Do Loop

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

Macro with Prompt Values and Do Loop

[ Edited ]

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.


Accepted Solutions
Solution
‎04-29-2017 10:17 PM
Super User
Super User
Posts: 6,845

Re: Macro with Prompt Values and Do Loop

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;

View solution in original post


All Replies
Super User
Posts: 19,155

Re: Macro with Prompt Values and Do Looo

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);

 

 

Super User
Super User
Posts: 6,845

Re: Macro with Prompt Values and Do Loop

[ Edited ]

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;

 

Contributor
Posts: 38

Re: Macro with Prompt Values and Do Loop

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:

example batters.JPG

 

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.

Solution
‎04-29-2017 10:17 PM
Super User
Super User
Posts: 6,845

Re: Macro with Prompt Values and Do Loop

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;
☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 245 views
  • 0 likes
  • 3 in conversation