Reading Multiple files with the same naming convention for the last 12 months and consolidating them

[TheNovice]

Hello Friends,

Somewhat of a beginner here.... I have a task that requires me to consolidate datasets for the previous 12 months before I proceed with the ask.

Example: I have an October Dataset and I would need to compare it against the previous 12 months. I know how to manually specify each dataset and then consolidate but that is tedious and ugly.

Therefore, I would like to specify the month and year and then have the script pull the previous 12 months of the data.

The datasets are all named the same: List_R_G but they reside in folders for different months.

I have no clue where to start. Could someone please assist ? Hopefully, my question makes sense

Thank you!

[Reeza]

1. Make a list of the file paths where the file will be.
2. In a data step, there's an option on the INFILE statement, FILEVAR.

See Example 5 in the documentation.
https://documentation.sas.com/?docsetId=lestmtsref&docsetTarget=n1rill4udj0tfun1fvce3j401plo.htm&doc...

[TheNovice]

Thank you so much for the response Reeza. However, this is my concern. The code you directed me to asks me to specify the file location. Meaning, I would to change this every month.

Ideally, what I would like to do is:

Month = November

Year = 2018 

have the query written so it goes back 12 months a picks all the files... So, technically if I change the month and year, it should compute back 12 months.

Is that possible ? As I said, the pathname is the same. It is just different folders in the format YYYY-MM that houses the datasets. Dataset names are the same as well.

Kindly advise and thank you very much again.

[Reeza]

You use a data step to create a list of the file names using a loop and INTNX(). This is dynamic so you can use TODAY() to get the date today and then use string functions to make the file path. Functions are pretty much all you need here, INTNX(), TODAY(), MONTH(), YEAR(), CATT() or CATX().

[TheNovice]

Thank you Reeza. I confess that I didn't understand all that. But I will look it up and learn more. I am familiar with intx() and the other functions except catt and catx. The problem is using it in the right context. I am still a new user that is more familiar with pulling data rather than manipulating and stitching it in different ways.

[SASKiwi]

A far easier way to do what you want would be to move all of your monthly SAS datasets into one folder and rename each of them with a monthly suffix.

Then to consolidate 12 months all you need to do is this:

data want;
  set MySASLib.List_R_G_201701 - MySASLib.List_R_G_201712;
run;

[TheNovice]

Oh, that is clever! Not what I am looking for in this case but I will be using that for another task. Thank you so much!

[Tom]

What code do you want to generate?  Is it something like this:

data want ;
  set
  "&top/2018-11/list_r_g"
  "&top/2018-10/list_r_g"
  "&top/2018-09/list_r_g"
  "&top/2018-08/list_r_g"
  "&top/2018-07/list_r_g"
  "&top/2018-06/list_r_g"
  "&top/2018-05/list_r_g"
  "&top/2018-04/list_r_g"
  "&top/2018-03/list_r_g"
  "&top/2018-02/list_r_g"
  "&top/2018-01/list_r_g"
  "&top/2017-12/list_r_g"
  ;
run;

You could write a simple data step to generate that list into a macro variable. You could then use it in your SET statement.

data _null_;
  length name list $500 ;
  do i=0 to -11 by -1 ;
     date = intnx('month',today(),i,'b');
     name = quote(catx('/','&top',substr(put(date,yymmdd10.),1,7),'list_r_g'));
     list = catx(' ', list , name);
  end;
  call symputx('list',list);
run;
%let top=/my/top/level/folder;
data want;
  set &list;
run;

[TheNovice]

Thank you Tom. I shall attempt this tomorrow. To answer your question, once a month I have to generate a dataset that has unique values (not appearing in the previous 12 months). So, as a first step I wanted to have a macro that pulls and consolidates the files for the previous 12 months. I just want to be able to specify a month and year and then have the macro work backwards.