topic Re: infile statement with macro in SAS Programming

infile statement with macro

dht115 — Sun, 19 Dec 2021 14:08:15 GMT

Hello,

I am running production job which needs to repeat every month by using SAS.

I have infile statement in my program which reads .dat datatype.

In the following infile statement, I do need to change 2021-12 (YYYY-MM) and 202112 (YYYYMM) every time I run the program.

INFILE 'C:\Users\John\Desktop\Files\2021-12-xx\SAS\SAS_C_202112.dat' LRECL=413
ENCODING="utf-8"

I do need to add a macro to change this two fields automatically according to current year and month.

Re: infile statement with macro

PaigeMiller — Sun, 19 Dec 2021 14:14:33 GMT

What is your question?

Can you show us what you have tried so far?

Re: infile statement with macro

dht115 — Sun, 19 Dec 2021 14:41:05 GMT

Hello,

I tried following:

%let var1 = 2021-12;

%let var2 = 202112;

INFILE 'C:\Users\John\Desktop\Files\&var1-xx\SAS\SAS_C_&var2.dat' LRECL=413
ENCODING="utf-8"

I need to identify month and year by using today() or date() function to make program run automatically.

I want something like this in macro variable output:

data test1;
enddate = (today());
enddate1 = (today());
format enddate yymmd7. enddate1 yymmn6.;
run;

Re: infile statement with macro

PaigeMiller — Sun, 19 Dec 2021 21:52:39 GMT

That's pretty good. You need to use double quotes (not single quotes) in your INFILE statement to get that part to work.

So here's how I would do this.

data _null_;
    call symputx('today1',put(today(),yymmd7.));
    call symputx('today2',put(today(),yymmn6.));
run;
%put &=today1;
%put &=today2;

infile "C:\Users\John\Desktop\Files\&today1\SAS\SAS_C_&today2..dat" LRECL=413 ENCODING="utf-8";

Note the double quotes in the INFILE statement, it won’t work with single quotes around the file name.

Also, note that I have created two macro variables (do not call them macros, they are not macros, they are macro variables, macros are different than macro variables), &TODAY1 which contains the value 2021-12 and &TODAY2 which contains the value 202112. I use a DATA step and CALL SYMPUTX to create these macro variables (you could also do this with a %LET statement, but then you also have to use %SYSFUNC() twice), and then I use a %PUT statement to make sure they have the desired values, which will change each month. The %PUT statements are optional, everything works without them. Lastly, there must be two dots after &today2 in the code, the first dot indicating the end of the macro variable’s name, and the second dot indicating an actual dot as part of the file name.

Re: infile statement with macro

s_lassen — Mon, 20 Dec 2021 09:23:34 GMT

Another possibility is to use the FILEVAR option on the INFILE statement:

data want;
  fnam=cats( 'C:\Users\John\Desktop\Files\',put(today(),yymmdd10.),'\SAS\SAS_C_',put(today(),yymmn6.),'.dat');
  INFILE dummy filevar=fnam LRECL=413ENCODING="utf-8" end=done;
  do while(not done);
    input <whatever you want to input>;
    output;
    end;
run;

- I assume the "-xx" meant that you want to have the current date (or some other date) in the filename.

If you want to read more that one file, that is easily accomplished, e.g.:

data want;
  format filedate yymmdd10.;
  do filedate=intnx('month',today(),0) to today();
    fnam=cats( 'C:\Users\John\Desktop\Files\',put(filedate,yymmdd10.),'\SAS\SAS_C_',put(filedate,yymmn6.),'.dat');
    INFILE dummy filevar=fnam LRECL=413ENCODING="utf-8" end=done;
    if fileexist(fnam) then do while(not done);
      input <whatever you want to input>;
      output;
      end;
    end;
run;

I put in a FILEEXIST call to check if the infile exists.

Re: infile statement with macro

dht115 — Mon, 20 Dec 2021 11:14:10 GMT

Is there a way for us to select previous month automatically by using similar structure?

In the following infile statement, I do need to change 2021-11 (YYYY-MM) and 202111 (YYYYMM) every time I run the program.

INFILE 'C:\Users\John\Desktop\Files\2021-11-xx\SAS\SAS_C_202111.dat' LRECL=413
ENCODING="utf-8"

I do need to add a macro variable to change this two fields automatically accordingly.

I am expecting to run this script in next month (Jan 2022). I do need macro variable that read 2021-12 and 202112.

*derive last month and year in yyyy-mm and yyyymm format. ;
*;
*need to convert this to sas macro variable;

*to use in infile statement;

data _null_;
   lastmonth = intnx('month',today(),-1,'E');
   put lastmonth= yymmd8.;
   lastmonth1 = intnx('month',today(),-1,'E');
   put lastmonth1= yymmn6.;
run;

Re: infile statement with macro

s_lassen — Mon, 20 Dec 2021 11:10:20 GMT

If "-xx" is literal, and not a placeholder for the date, you can do it like this:

data want;
  Last_month=intnx('month',today(),-1);
  fnam=cats( 'C:\Users\John\Desktop\Files\',put(Last_month,yymmd7.),'-xx\SAS\SAS_C_',put(Last_month,yymmn6.),'.dat');
  INFILE dummy filevar=fnam LRECL=413ENCODING="utf-8" end=done;
  do while(not done);
    input <whatever you want to input>;
    output;
    end;
run;

Or, if you want to loop through all the dates for the last month:

data want;
  format filedate yymmdd10.;
  do filedate=intnx('month',today(),-1) to intnx('month',today(),-1,'E');
    fnam=cats( 'C:\Users\John\Desktop\Files\',put(filedate,yymmdd10.),'\SAS\SAS_C_',put(filedate,yymmn6.),'.dat');
    INFILE dummy filevar=fnam LRECL=413ENCODING="utf-8" end=done;
    if fileexist(fnam) then do while(not done);
      input <whatever you want to input>;
      output;
      end;
    end;
run;

The 'E' or 'END' parameter to INTNX means to take the end of the period, if the parameter is omitted, it uses the beginning as default.

Re: infile statement with macro

PaigeMiller — Mon, 20 Dec 2021 11:16:09 GMT

@dht115 wrote:

Is there a way for us to select previous month automatically by using similar structure?

In the following infile statement, I do need to change 2021-11 (YYYY-MM) and 202111 (YYYYMM) every time I run the program.

INFILE 'C:\Users\John\Desktop\Files\2021-11-xx\SAS\SAS_C_202111.dat' LRECL=413
ENCODING="utf-8"

I do need to add a macro variable to change this two fields automatically accordingly.

I am expecting to run this script in next month (Jan 2022). I do need macro variable that read 2021-12 and 202112.

Its always helpful to state the real problem in your original message, that way we don't waste time producing solutions that don't work for the real problem you have.

Change the original solution I gave to:

data _null_;
    call symputx('today1',put(intnx('month',today(),-1,'b'),yymmd7.));
    call symputx('today2',put(intnx('month',today(),-1,'b'),yymmn6.));
run;

Re: infile statement with macro

dht115 — Mon, 20 Dec 2021 11:20:03 GMT

Thank you it works.