BookmarkSubscribeRSS Feed
Quartz | Level 8

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!


Super User
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.
Quartz | Level 8

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.



Super User
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().
Quartz | Level 8
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.
Opal | Level 21

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;
Quartz | Level 8

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!

Super User Tom
Super User

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

data want ;

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);
  call symputx('list',list);
%let top=/my/top/level/folder;
data want;
  set &list;
Quartz | Level 8
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.



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 4 in conversation