Fail to use macro to import multiple txt files

[xizidememeda]

Hello guys, I write a sas macro to import multiple txt files as follows, however, it fails no matter how I improve it.

The log says the physical files don't exist.

Can anyone help me? 

%macro all_on_index(syear=, eyear=);

%do year = &syear %to &eyear;
	%do month = 1 %to 12;
	%let ym = %eval(&year*100 + &month);

data work.op;
 infile 'D:files\price_&ym..txt' dsd dlm='09'x truncover;
 input var1:$20.     var2:YYMMDD8.;

%if year = &syear and month = 1 %then %do;
	data work._all;
		set work.op;
	run;
	%end;
%else %do;
	data work._all;
		set work._all work.op;
	run;
	%end;

	%end;
%end;

%mend all_options_on_index;

I will appreciate it very much for your help!!

[Kurt_Bremser · Accepted solution]

I marked the issues:

%macro all_on_index(syear=, eyear=);
%local year month ym; /* make your macro behave if it's called from another macro that uses the same variables */
%do year = &syear %to &eyear;
  %do month = 1 %to 12;
    %let ym = %eval(&year*100 + &month);

data work.op;
infile "D:files\price_&ym..txt" dsd dlm='09'x truncover; /* use double quotes to resolve macro variables */
input var1:$20. var2:YYMMDD8.;
run; /* always a good idea to define clear step boundaries */

    %if &year = &syear and month = 1 %then %do; /* "year" is just a string, use &year for the macro variable */

data work._all;
set work.op;
run;

    %end;
    %else %do;

data work._all;
set work._all work.op;
run;

    %end;
  %end;
%end;

%mend all_options_on_index;

You can simplify the code:

%macro all_on_index(syear=, eyear=);
%local year month ym;
%do year = &syear %to &eyear;
  %do month = 1 %to 12;
    %let ym = %eval(&year*100 + &month);

data work.op;
infile "D:files\price_&ym..txt" dsd dlm='09'x truncover;
input var1:$20. var2:YYMMDD8.;
run;

data work._all;
set
%if &year ne &syear or &month ne 1 %then work._all;
  work.op
;
run;

  %end;
%end;

%mend all_options_on_index;

[xizidememeda]

I rewrite my complete codes based on your reply and comments and it works very well!

Thanks very much for your help!!

[gamotte]

Hello, macro variables inside single quoted strings are not resolved.
Try
infile "D:files\price_&ym..txt" dsd dlm='09'x truncover;
instead.

[xizidememeda]

Yes, you are right! Many many thanks for your help!

[Reeza]

Since all your files are the same you don't need to use a macro, you can use a wildcard and read them all in one step.