Extracting years from input files over time interval [SAS]

Question

I'm using SAS macros to run through multiple monthly files and extract variables needed for further analysis. Currently the program takes each monthly file, extracts what's needed and outputs it as the same monthly file. I also have it set to combine everything into a yearly file. This works fine, for the most part:

%let start_date = '31jan2022'd;
%let end_date = '31mar2022'd;
%let num_years = %sysfunc(intck(year,&start_date,&end_date));

data _null_;
     call symput('start_loop',compress(intck('month',&start_date,date())*-1));
     call symput('end_loop',compress(intck('month',&end_date,date())*-1));

%MACRO MONTH_EXTRACT;

%do l=&start_loop. %to &end_loop.;
     data _null_;
          call symput ('monyy',put(intnx('month',date(),&l.,'end'),monyy5.));
          call symput ('end_mon',put(intnx('month',date(),&l.,'end'),date9.));
          call symput ('date',put(intnx('month',date(),&l.,'end'),yymmn.));
     run;

     &let file=libref.monthly_&date.;
     %let file_year=%substr(&date,1,4);

     data file_&monyy. (keep=var1 var2 var3);
     set &file;
     inpt_dt=&date;
     proc append base=files_&file_year force data=file_&monyy.;
%end;
%mend;

This works fine when the start date and the end date are contained within the same year. However, what is desired is that, when the start date and end date are not within the same year, a yearly file will be compiled for each year within the interval. Ex.

    start_date = '31oct2021'd;
    end_date = '31mar2022'd;

This would generate two outputs called files_2021 and files_2022. When I run it with the current code, it only generates the first file for 2021.

I've attempted to add in:

%IF &num_years > 1 %then %do;
     %LET start_year=input(substr(&start_date,9,-4),4.0);
     start_month=input(&start_date,mmddyy10.);
     %LET start_month=substr(start_month,1,2);
     %LET end_year=input(substr(%end_date,9,-4),4.0);
     end_month=input(&end_date,mmddyy10.);
     %LET end_month=substr(end_month,1,2);
     %DO file_interval=&start_year %to &end_year;
           months=0;
           %if file_interval=&start_year %then %do
                 mstart=&start_month;
           %end;
           %else %do
                 mstart=1;
           %end;
           %if file_interval=&end_year %then %do
                 mstop=&end_month;
           %end;
           %else %do
                mstop = 12;
           %end;
           months=mstop-mstart+1;
    %end;

I know that a proc append is needed, but I don't know what would be the data to append in this instance. I also know that right now I'm only counting the months. How can I isolate each year and create yearly output files from the monthly?

user_5342700 · Answer

Instead of looping by year and calculating the month, loop by month and calculate the year. You can increment your loop by referencing off of the start date. For example:

Start Date = 01JAN2020

End Date = 01MAR2020

01JAN2020: 0 Months from Start Date
01FEB2020: 1 Month from Start Date
01MAR2020: 2 Months from Start Date

We can use a combination of intck() and intnx() to calculate this.

%let start_date = 31oct2021;
%let end_date   = 31mar2022;

%macro month_extract;

    %do i = 0 %to %sysfunc(intck(month, "&start_date."d, "&end_date."d) );

        %let date  = %sysfunc(intnx(month, "&start_date."d, &i.) );
        %let month = %sysfunc(month(&date.), z2.);
        %let year  = %sysfunc(year(&date.) );

        %let file    = libref.monthly_&year.&month.;
        %let outfile = files_&year.;

        %put File: Year: &year. | Month: &month. | File: &file. | Outfile: &outfile.;

    %end;

%mend;
%month_extract;

Output:

File: Year: 2021 | Month: 10 | File: libref.monthly_202110 | Outfile: files_2021
File: Year: 2021 | Month: 11 | File: libref.monthly_202111 | Outfile: files_2021
File: Year: 2021 | Month: 12 | File: libref.monthly_202112 | Outfile: files_2021
File: Year: 2022 | Month: 01 | File: libref.monthly_202201 | Outfile: files_2022
File: Year: 2022 | Month: 02 | File: libref.monthly_202202 | Outfile: files_2022
File: Year: 2022 | Month: 03 | File: libref.monthly_202203 | Outfile: files_2022

Extracting years from input files over time interval [SAS]

1 Answers