I have daily tables and i want to produce an up2date monthly table.
so my table names are tab20080901, tab20080902, up to tab20080915(being yesterday).
so i would like to combine these to produce a table tab200809.
But if i run this tomorrow i would like to, obviously, include tab20080916.
so it needs to know what month and day it, and then combine all necessary tables.
I've been told this is probably a job for Array man!!
You can either choose a SAS macro language approach or a use a SAS DATA step, in order to generate the SAS execution code each day inline for your purpose.
A DATA step approach would involve using the INTNX function to step through each date, building SAS code with PUT statements, out to a "temp" sequential file allocation, and then use %INCLUDE to run your "generated" SAS code.
FILENAME TEMPSAS TEMP;
MSTARTDT = INTNX('MONTH',TODAY(),0);
PUT 'DATA tab' MSTARTDT YYMMN6. ';';
PUT 'SET' @;
DO MYDATE=MSTARTDT TO TODAY()-1;
PUT ' tab' MYDATE YYMMDDN8. @;
PUT '; ' / 'RUN;';
Some similar macro language logic would also apply, except there is no need to send the SAS code to an external file -- it would be compiled by SAS and then executed.
Here 3 variants how you could do it. I prefer variant 3 with the view.
Variant 2 creates a physical table but at least doesn't process all the daily tables all the time; but: it aborts with an error if there is no new daily table for a specific rundate.
Variant 1 works also but passes all the daily tables with every run. This is not the most efficient way to do things - and your job will take longer and longer the more you come to the end of a month (that's also true for variant 2).
Variant 3 builds only a view. This is very fast, runs at the end of the month almost as fast as at the beginning and doesnt' duplicate data (storage).
/* Create some test tables */
data work.Tab20080801 work.Tab20080901 work.Tab20080902 work.Tab20080903 work.Tab20080904 work.Tab20080906
/* Get the date for which tables have to be selected */
If a run on the first day of a new month has to pick the tables from the
previous month then use this one:
/* select all Tablenames from the current month */
proc sql noprint;
select memname into :MonthTblList separated by ' '
where libname='WORK' and memname like "TAB%sysfunc(putn(&datekey,yymmN6.))%%"
order by memname;
/* variant 1: */
/* create the month table physically everytime from scratch */
/* variant 2: */
/* add only the latest day table to the month table */
proc append base=MonthTab%sysfunc(putn(&datekey,yymmN6.))
/* variant 3: */
/* create a monthly view for all existing tables of a month */
data VMonthTab%sysfunc(putn(&datekey,yymmN6.)) / view=VMonthTab%sysfunc(putn(&datekey,yymmN6.));
Message was edited by: Patrick