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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

4 REPLIES 4
Reeza
Super User

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

 

 

Tom
Super User Tom
Super User

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;

 

jl1005
Obsidian | Level 7

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.

Tom
Super User Tom
Super User

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;

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
  • 4 replies
  • 1364 views
  • 0 likes
  • 3 in conversation