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 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.
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;
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;
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.
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.