macro file import nested do loops

[kb011235]

Hi, i'm trying to import 4 files using a couple of loops within a macro. The code runs without error, however it doesn't ever use '98765' in the j loop; specifically it imports 12345medstatus.txt and 12345rxstatus.txt but doesn't import 98765medstatus.txt or 98765rxstatus.txt. 

%let claim_type = med/rx ; 
%let issuer_ids = 12345/98765 ; 

%macro impt;

%let _len_i=%eval(%sysfunc(countc(&claim_type.,/)) + 1);
%let _len_j=%eval(%sysfunc(countc(&issuer_ids.,/)) + 1);

%do i = 1 %to &_len_i.;

%let clm_tp = %scan(&claim_type.,&i.);

%do j = 1 %to &_len_j.;

%let iss_id = %scan(&issuer_ids.,&j.);

PROC IMPORT
datafile = "/sasuser/import/&iss_id.&clm_tp.status.txt"
out = tmp_impt_&iss_id.&clm_tp.
dbms = tab
replace ;
getnames = no ;
guessingrows = 32767 ;
run ; 

PROC APPEND base = &clm_tp.status data = tmp_impt_&iss_id.&clm_tp.; 
run;

%end;
%end;

%mend impt;

%impt;

I confirmed that %scan(&issuer_ids.,2)=98765. Any ideas? 

[ballardw]

What does your LOG show?

You may want to turn on Option mprint; prior to running the macro to get a better log.

options mprint;

%impt;

options nomprint;

Copy your log text and paste into a text box opened on the forum with the </> icon.

[Reeza]

For 4 files? Does this need to scale? Do all file's have the same layout and format? If so you'd be better off reading all at once instead of the macro loop within a single data step. It'll definitely scale better and be more efficient all around.

[Kurt_Bremser]

Don't use PROC IMPORT for delimited (or other) text files. The data step is easily written and produces consistent data, something you can't expect from PROC IMPORT.

Given that, you are much better off reading all files of similar structure in one data step, using a proper wildcarded filename on the INFILE statement, combined with a FILENAME= option to record the name of the currently read file.

[kb011235]

mea culpa, my first post omitted a macro our team uses for moving files between our unix and windows servers. It was my first attempt at creating a loop with non-numeric values so thought the error must have had to do with my code. After rereading the log file, the issue was that the unix/windows macro must also use a "j" variable because when my code completed its j=1 loop, the value of "j" had jumped to 4. This is what caused the program to exit the loop before using the second "j" element. The issue was fixed by renaming "j" to "jj" in my program. 

%let end_date = '30SEP2020'd;

%let datayear = 2019;
%let fl_dt = %sysfunc(putn("&end_date."d,yymmddn.));
%let xprt_pth = %sysfunc(getoption(work)) ; /*get working directory*/
%let fldr_nm = 2020_05_Final ; /*Folder where the status files are saved*/

%global unix_dir win_dir file_names;

%let claim_type = med/rx ; /*This is used for the status file names.*/
%let issuer_ids = 12345/98765; /*This is used for the status file names.*/

%macro impt;

	%let _len_i=%eval(%sysfunc(countc(&claim_type.,/)) + 1);
	%let _len_j=%eval(%sysfunc(countc(&issuer_ids.,/)) + 1);
	
	%do i = 1 %to &_len_i.;
		
		%let clm_tp = %scan(&claim_type.,&i.);
		
		%do jj = 1 %to &_len_j.;
		
			%let iss_id = %scan(&issuer_ids.,&jj.);
			
			/*Import and load status files*/
			%let unix_dir=&xprt_pth.;
			%let win_dir=\winfilepath\&datayear.\&fldr_nm.;
			%let file_names=&iss_id._&clm_tp.status.txt;
			%to_unix;
			
			DATA tmp_&clm_tp._&iss_id. ;
			infile "&xprt_pth./&file_names." delimiter = '09'x MISSOVER DSD lrecl=32767 firstobs=1 ;
			  informat clm_nbr $13. ;
			  informat claim_status 10. ;
			  informat claim_type $10. ;
			  informat inactivation_dt $19. ;
			  informat original_claim_id $13. ;
			  format clm_nbr $13. ;
			  format claim_status 10. ;
			  format claim_type $10. ;
			  format inactivation_dt $19. ;
			  format original_claim_id $13. ;
			input
			  clm_nbr 
			  claim_status 
			  claim_type
			  inactivation_dt 
			  original_claim_id ;
			RUN ;		
			
		%end;
	%end;
	
%mend impt;

%impt;

@Reeza at this time, it doesn't need to scale. I wanted to use this as an opportunity to learn how to loop over non-numeric values. I come across a lot of similar situations in my work. My first attempt was using arrays, but couldn't get that method to work. Here's what I had initially:

DATA _null_;
array claim_type{2} $ ("med","rx") ;
array issuer_ids{2} (12345,98765) ;
run;

%macro impt;
	
	%do i = 1 %to dim(claim_type);
		
		%do jj = 1 %to dim(issuer_ids);
			
			/*Import and load status files*/
			%let unix_dir=&xprt_pth.;
			%let win_dir=\winfilepath\&datayear.\&fldr_nm.;
			%let file_names=issuer_id{&jj.}_claim_type{&i.}status.txt;
			%to_unix;
			
			DATA tmp_claim_type{&i.}_issuer_id{&jj.};
			infile "&xprt_pth./&file_names." delimiter = '09'x MISSOVER DSD lrecl=32767 firstobs=1 ;
			  informat clm_nbr $13. ;
			  informat claim_status 10. ;
			  informat claim_type $10. ;
			  informat inactivation_dt $19. ;
			  informat original_claim_id $13. ;
			  format clm_nbr $13. ;
			  format claim_status 10. ;
			  format claim_type $10. ;
			  format inactivation_dt $19. ;
			  format original_claim_id $13. ;
			input
			  clm_nbr 
			  claim_status 
			  claim_type
			  inactivation_dt 
			  original_claim_id ;
			RUN ;		
			
		%end;
	%end;
	
%mend impt;

%impt;

Here's the log:

279  DATA _null_;
280  array claim_type{2} $ ("med","rx") ;
281  array issuer_ids{2} (12345,98765) ;
282  run;

NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.00 seconds


283
284  %macro impt;
285  	
286  	%do i = 1 %to dim(claim_type);
287  		
288  		%do jj = 1 %to dim(issuer_ids);
289  			
290  			/*Import and load status files*/
291  			%let unix_dir=&xprt_pth.;
292  			%let win_dir=\winfilepath\&datayear.\&fldr_nm.;
293  			%let file_names=issuer_id{&jj.}_claim_type{&i.}status.txt;
294  			%to_unix;
295  			
296  			DATA tmp_claim_type{&i.}_issuer_id{&jj.};
297  			infile "&xprt_pth./&file_names." delimiter = '09'x MISSOVER DSD lrecl=32767 firstobs=1 ;
298  			  informat clm_nbr $13. ;
299  			  informat claim_status 10. ;
300  			  informat claim_type $10. ;
301  			  informat inactivation_dt $19. ;
302  			  informat original_claim_id $13. ;
303  			  format clm_nbr $13. ;
304  			  format claim_status 10. ;
305  			  format claim_type $10. ;
306  			  format inactivation_dt $19. ;
307  			  format original_claim_id $13. ;
308  			input
309  			  clm_nbr
310  			  claim_status
311  			  claim_type
312  			  inactivation_dt
313  			  original_claim_id ;
314  			RUN ;		
315  			
316  		%end;
317  	%end;
318  	
319  %mend impt;
320
321  %impt;
NOTE: The macro IMPT is executing from memory.
      34 instructions 1384 bytes.
ERROR: Required operator not found in expression: dim(claim_type)
ERROR: The %TO value of the %DO I loop is invalid.
ERROR: The macro IMPT will stop executing.

I thought maybe the dim functions weren't being evaluated, so wrapped the loop upper bounds in %sysfunc() and here is the error message:

ERROR: The function DIM referenced by %SYSFUNC, %QSYSFUNC, or %SYSCALL cannot be used within the MACRO function/call-routine interfaces.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: %sysfunc(dim(claim_type))
ERROR: The %TO value of the %DO I loop is invalid.
ERROR: The macro IMPT will stop executing.

Is there a way to perform this import using arrays or is there a more concise way to import similarly named files?

12345medstatus.txt

98765medstatus.txt

12345rxstatus.txt

98765rxstatus.txt

[Reeza]

Check the macro appendix in the documentation for examples on how to loop through dates and non numeric indexes and such.

[Reeza]

See my post here for a more concise method to read multiple files that have the same layout but the names don't matter too much.

https://communities.sas.com/t5/SAS-Communities-Library/How-do-I-write-a-macro-to-import-multiple-tex...

This is part two of an ongoing series into how to accomplish a task WITHOUT using a macro.

In the previous post, I split a file without using a macro. In this post, I go over how

read in text files that have the same format and create a single file.

One method of doing so is:

1. Use the OS to generate a list of file names
2. Write the code to read a single file
3. Convert that to a macro
4. Read each file in using the macro and append the results

Instead of this method, SAS provides a wildcard method of referring to files.

This means if my file reference looks like:

"/folders/myfolders/*.csv"

SAS will read all CSV folders in the folder myfolders.

You can limit your wild card as well using a more specific naming convention....

The SAS documentation for the INFILE statement also includes a FILENAME & FILEVAR option that allows you read from a list of files, either dynamically generated or from a data set. 

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=lestmtsref&docsetTarget=n...

[kb011235]

Thanks everyone

[Tom]

After rereading the log file, the issue was that the unix/windows macro must also use a "j" variable because when my code completed its j=1 loop, the value of "j" had jumped to 4. 

That is caused by the macro using the macro variable I without first defining it as LOCAL to the macro.  Your posted macro also fails to define its local macro variables.  Making the macro variables your macro uses local will not protect them from being modified by that rouge macro you are calling, but it will protect other macros that might call your macro from having their macro variables overwritten.