Arrays and Variable

[deleted_user]

Hi

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

Any ideas?

Si

[sbb]

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.

An example:

FILENAME TEMPSAS TEMP;
DATA _NULL_;
FILE TEMPSAS;
MSTARTDT = INTNX('MONTH',TODAY(),0);
PUT 'DATA tab' MSTARTDT YYMMN6. ';';
PUT 'SET' @;
DO MYDATE=MSTARTDT TO TODAY()-1;
PUT ' tab' MYDATE YYMMDDN8. @;
END;
PUT '; ' / 'RUN;';
RUN;
%INCLUDE TEMPSAS;

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.

Hope that helps.

Scott Barry
SBBWorks, Inc.

[Patrick]

Hi Spud

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

HTH
Patrick

/* Create some test tables */
data work.Tab20080801 work.Tab20080901 work.Tab20080902 work.Tab20080903 work.Tab20080904 work.Tab20080906
work.Tab%sysfunc(today(),yymmddN8.) work.Tab20081020;
var='some value';
run;

/* Get the date for which tables have to be selected */
%let DateKey=%sysfunc(today());

/*******
If a run on the first day of a new month has to pick the tables from the
previous month then use this one:
%let DateKey=%eval(%sysfunc(today())-1);
*******/

/* select all Tablenames from the current month */
proc sql noprint;
select memname into :MonthTblList separated by ' '
from dictionary.members
where libname='WORK' and memname like "TAB%sysfunc(putn(&datekey,yymmN6.))%%"
order by memname;
%put MonthTblList;
quit;

/* variant 1: */
/* create the month table physically everytime from scratch */
data MonthTab%sysfunc(putn(&datekey,yymmN6.));
set &MonthTblList;
run;

/* variant 2: */
/* add only the latest day table to the month table */
proc append base=MonthTab%sysfunc(putn(&datekey,yymmN6.))
data=Tab%sysfunc(putn(&datekey,yymmddN8.))
force;
quit;run;

/* 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.));
set &MonthTblList;
run; Message was edited by: Patrick

[deleted_user]

Thanks patrick and sbb