Iterative Do Loop

[raivester]

Hello--I am trying to read in and create a working data set for a number of data sets with the same naming convention but a unique identifier for each year. The code below does not achieve this. Does anyone see the flaw(s) in this logic?

%macro iterm(lst);
	%let finish=%sysfunc(countw(&lst));
		%do i = 1 %to &finish;
		%let year=%scan(&lst,&i);
			proc import datafile="&maindir.\map_data_&year..csv"
				out=work.over12_&year.
				dbms=csv
				replace;
			run;  
		%end;
%mend iterm;

%iterm(1718 1617 1516 1415 1314 1213 1112 1011 0910 0809 0708 0607 0506 0405 0304 0203);

[jimbarbour]

Can you post the log?

Can you give us a list of some of the actual dataset names?  

This doesn't look too bad.  We should be able to get this ironed out.

Jim

[raivester]

Hi Jim,

Thanks for the thoughtful questions/response. The log is below. You'll see I added the locals you suggested. The dataset names are as follows: map_data_1718, map_data_1617 . . . , map_data_0203. (I know these years looks strange, but, for example, the first instance represents 2017/2018.) I have doublechecked that the mairdir macro points to the correct directory.

*** over 12;
158
159  %macro iterm(lst);
ERROR: The macro ITERM is still executing and cannot be redefined.
ERROR: A dummy macro will be compiled.
160      %local finish;
161      %local i;
162      %local year;
163      %let finish=%sysfunc(countw(&lst));
164          %do i = 1 %to &finish;
165          %let year=%scan(&lst,&i);
166              proc import datafile="&maindir.\map_data_&year..csv"
167                  out=work.over12_&year.
168                  dbms=csv
169                  replace;
170              run;
171          %end;
172  %mend iterm;
173
174  %iterm(1718 1617 1516 1415 1314 1213 1112 1011 0910 0809 0708 0607 0506 0405 0304 0203);

[Kurt_Bremser · Accepted solution]

Shut down your SAS session and start a new one (disconnect from the workspace server if working with Enterprise Guide). You have an unfinished macro call somewhere upstream, probably because of a missing closing bracket.

If the problem persists after a restart, carefully work through your log from the top down and clean out any issues (ERRORs, WARNINGs, extraneous NOTEs) and especially look for a %item statement without a closing bracket.

[jimbarbour]

Ah, so the log did in fact matter.

Sounds like we're all set now. 👍

Jim

[Kurt_Bremser]

As I often state: that's why Maxim 2 is so high up on the list. And why we always ask people to post their log in proper fashion, even when we already have a good suspicion what the problem is.

The log is our #1 diagnostic tool.

[raivester]

Noted!

[jimbarbour]

Also, can you insert the following code after your %MACRO statement?

	%LOCAL	Finish;
	%LOCAL	Year;
	%LOCAL	i;

I think it's irrelevant in this case, but every once in a while you'll have a macro variable being set elsewhere that causes a "macro collision."  The way your code is structured, I don't think this is the case at all, but it can't hurt.

I checked the basic flow of your macro (see log below), so I don't see anything fundamentally wrong there.  Those are some funny looking "years."  Are those really years?  I'd definitely like to see the log, and I'd like to see some actual data set names.

Lastly, are you pointing to the correct directory?  I know, silly question, but sometimes I miss the obvious, particularly when I'm tired.

Jim

Log:

         %iterm(1718 1617 1516 1415 1314 1213 1112 1011 0910 0809 0708 0607 0506 0405 0304 0203);
NOTE:  FINISH=16
NOTE:  I=1 YEAR=1718
NOTE:  I=2 YEAR=1617
NOTE:  I=3 YEAR=1516
NOTE:  I=4 YEAR=1415
NOTE:  I=5 YEAR=1314
NOTE:  I=6 YEAR=1213
NOTE:  I=7 YEAR=1112
NOTE:  I=8 YEAR=1011
NOTE:  I=9 YEAR=0910
NOTE:  I=10 YEAR=0809
NOTE:  I=11 YEAR=0708
NOTE:  I=12 YEAR=0607
NOTE:  I=13 YEAR=0506
NOTE:  I=14 YEAR=0405
NOTE:  I=15 YEAR=0304
NOTE:  I=16 YEAR=0203

[ballardw]

Where is the Maindir macro variable set? To what value?

If you expect all of the resulting sets to be of the same structure you would be better off writing a data step that reads the data as desired. Otherwise each of those data sets created by proc import can have different variable names, variable types for the same variables, differing types for the same variable name, different lengths for character variables.

You should likely add the option Guessingrows=max to the proc import code. Import one file. The log should contain the data step used to read the file. Copy the code to the editor and clean it up. Check to see if the variables that are character are long enough to contain expected values, that things you won't do arithmetic with like account numbers, id numbers and such are character, that the values that should be numeric are read with a numeric informat.

Once you have that code working that is where the loop should read the different files and create different output data sets. Then all the sets would have the same properties.

And show the log for the code ran. Copy from the log and paste on the forum into a box opened using the </> icon.