topic Re: Fail to use macro to import multiple txt files in SAS Programming

Fail to use macro to import multiple txt files

xizidememeda — Thu, 03 Oct 2019 10:29:51 GMT

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!!

Re: Fail to use macro to import multiple txt files

Kurt_Bremser — Thu, 03 Oct 2019 11:13:45 GMT

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;

Re: Fail to use macro to import multiple txt files

gamotte — Thu, 03 Oct 2019 11:12:03 GMT

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

Re: Fail to use macro to import multiple txt files

xizidememeda — Thu, 03 Oct 2019 11:32:59 GMT

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

Thanks very much for your help!!

Re: Fail to use macro to import multiple txt files

xizidememeda — Thu, 03 Oct 2019 11:34:23 GMT

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

Re: Fail to use macro to import multiple txt files

Reeza — Thu, 03 Oct 2019 15:15:51 GMT

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.