topic Re: Reading Multiple files with the same naming convention for the last 12 months and consolidating in SAS Procedures

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

TheNovice — Wed, 28 Nov 2018 01:08:48 GMT

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!

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

Reeza — Wed, 28 Nov 2018 01:46:03 GMT

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&docsetVersion=9.4&locale=en#n1x9joj5qbfkjyn1kqtm7joru0qb

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

TheNovice — Wed, 28 Nov 2018 05:52:41 GMT

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.

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

Reeza — Wed, 28 Nov 2018 16:02:25 GMT

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().

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

SASKiwi — Wed, 28 Nov 2018 19:29:17 GMT

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;

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

Tom — Wed, 28 Nov 2018 19:37:42 GMT

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;

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

TheNovice — Sat, 01 Dec 2018 07:26:46 GMT

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!

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

TheNovice — Sat, 01 Dec 2018 07:30:49 GMT

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.

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

TheNovice — Sat, 01 Dec 2018 07:32:48 GMT

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.