Macros date ranges

[samface]

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

[ballardw]

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.

[samface]

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?

[Reeza · Accepted solution]

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

[samface]

Thanks for the code @Reeza but i am not able to get the results I am waiting for.

[Reeza]

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.

[samface]

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!!