Hello everyone,
I need help designing a SAS macro procedure that will allow the aggregation of my tables by dates. For example in I have the following tables in my database:
My library name is data.
Here are the tables within my library:
data.sample_201805
data.sample_201806
data.sample_201807
data.sample_201808
data.sample_201809
I want to be able to call a macro that will create a new table by periods (quarterly, semiannually, yearly, etc...) For instance for quarter 3: I want to be able to create a table with start date: sample_201806 and for end date: sample_201809.
I can do it manually with this code below, but it is really time consuming. Hope someone one will be able to help. Thanks.
data quarter_3;
set
data.sample_201806
data.sample_201807
data.sample_201808
data.sample_201809
run;
Thanks
This is the basic idea. You'll have to look up INTNX to see what the valid intervals are and if it's close to what you want. You may need to modify it to handle all the intervals you need and to ensure the dates align but it's enough to get you started.
Didn't test this because I don't have data.
Rather than set you could also loop with PROC APPEND to combine the data. Not sure if it's faster or not, but may be since it copies the file at once. If you wanted to do any calculations or add in any values you wouldn't be able to do that though.
%macro combine_data(start_date=, interval=, increment=, dsout=); data demo; start_date = input("&start_date", yymmdd10.); end_date =intnx("&interval", start_date, &increment, 'b'); format start_date end_date yymmn6.; *create macro variables; call symputx('date_s', start_date); call symputx('date_e', end_date); run; data &dsout.; set Data&date_s. - Data&date_e.; run; %mend; %combine_data(start_date=20180101, interval=Quarter, increment=3, dsout=TEST123);
If you are allows dealing with sequential named files then
set data.sample_201806 - data.sample_201809 ;
run;
Will suffice, so you only "need" two macro variables.
Thanks for the quick reply. I was in fact able to repodruce the tables using the iphen. Thanks for this tip, would you know how I could use a macro to do this?
This is the basic idea. You'll have to look up INTNX to see what the valid intervals are and if it's close to what you want. You may need to modify it to handle all the intervals you need and to ensure the dates align but it's enough to get you started.
Didn't test this because I don't have data.
Rather than set you could also loop with PROC APPEND to combine the data. Not sure if it's faster or not, but may be since it copies the file at once. If you wanted to do any calculations or add in any values you wouldn't be able to do that though.
%macro combine_data(start_date=, interval=, increment=, dsout=); data demo; start_date = input("&start_date", yymmdd10.); end_date =intnx("&interval", start_date, &increment, 'b'); format start_date end_date yymmn6.; *create macro variables; call symputx('date_s', start_date); call symputx('date_e', end_date); run; data &dsout.; set Data&date_s. - Data&date_e.; run; %mend; %combine_data(start_date=20180101, interval=Quarter, increment=3, dsout=TEST123);
I know this is not a full solution, it's intended to get you started, I"m assuming you would tailor it to your circumstances. What did you try? What exactly doesn't work? Post your code and log.
And, quarter 3 doesn't make sense to be only 3 months. That to me would be Month 3. A Quarter is 3 months by definition so you may want to clarify your terminology or consider aligning it with SAS's to make it easier. If you're going to use your own definitions you'll have to add a lot of IF/THEN conditions to your data step.
Thanks again @Reeza and @ballardw I was able to get the results expected. I basically defined a macro with beginning and ending tables as follow:
%maco combine(table_beg=, table_end=, dsout=);
data &dsout.;
set libname.&table_beg. - libname.&table_end.;
run;
%mend;
%combine (table_beg= , table_end= , dsout= ,)
Hopefully this code will guide someone else.
thanks again guys!!
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.