topic Re: Appending dated tables in SAS Programming

Appending dated tables

Perreby — Tue, 27 Feb 2018 07:48:58 GMT

Im pretty decent with SAS coding but have one thing that is getting the better of me.

I have various datasets that is dated for each month. Say X_201501, X_201502 ... X201601;

Now I would like a use to input the current month and 12months prior;

So %let currentmonth= 201601;

and %let previousyearmonth=201501;

I would then like to append all the tables from 201501 to 201601.

I used the following code:

Data Combined;

set X_&currentmonth.- X_&previousyearmonth.;run;

The problem is that it is now looking for tables such as 201579 and 201580 all the way until it gets to 201601.

But we all know there are only 12months in a year. So essentially it should only look for the following tables:

X_201501, X_201502, X_201503, X_201504, X_201505, X_201506, X_201507, X_201508, X_201509,X_201510,X_201511, X_01512, X_201601

So how does one code this so SAS only adds the above tables. The ideal would be that I dont have to request users to input all the above 12months in the data.

Regards

Riaan

Re: Appending dated tables

Oligolas — Tue, 27 Feb 2018 08:47:41 GMT

Hi,

try this:

data x_201501 X_201502 X201601 X201602;
   length ds $20;
   set sashelp.class;
run;

%symdel dsnames inputds /nowarn;
%let inputds=X201601;
PROC SQL noprint;
   select memname into :dsnames separated by ' '
   from sashelp.vtable
   where libname eq 'WORK'
   and intnx('year',input(compress("&inputds.",,'dk'),yymmn6.),-1)<=input(compress(memname,,'dk'),yymmn6.)<=input(compress("&inputds.",,'dk'),yymmn6.)
   ;
QUIT;
%put &=dsnames.;

data combined;
   set &dsnames.;
run;
%symdel dsnames inputds;

Re: Appending dated tables

RW9 — Tue, 27 Feb 2018 09:58:35 GMT

Why are the data stored like that? From a storage point of view having multiple tables with similar data is inefficient - you have the extra overhead not only of the header block, but a read/write on each. From a programming point of view it is inefficient - as you are finding now, trying to code with such a setup is a nightmare, creating masses of messy macro code. Imagine how much simpler life would be if you stored all that data in one big dataset, with a column (you know those parts of the datasets used to capture data) for date, then your code would be as simple as:

data want;
  set have;
  where date between today() and intnx('month',today(),-1);
run;

Its your data modelling causing your issues - and most likely issues across the board.

Re: Appending dated tables

Perreby — Tue, 27 Feb 2018 10:14:00 GMT

Thanks for the response.

Found a round about way of doing it.

See below:

%let currentmonth=201601;

%let currentyear=2016;
%let currentmonth=01;

%let previousmonth=201501;
%let previousyear=2015;
%let previousmonth=01;

Data Prevyear;
set work.exp_&previousyear.&previousmonth.-work.exp_&previousyear.12;run;

Data currentyear;
set work.exp_&currentyear.01-work.exp_&currentyear.&currentmonth.;run;

PROC SQL;
CREATE TABLE WORK.Append_Table AS
SELECT * FROM WORK.PREVYEAR
OUTER UNION CORR
SELECT * FROM WORK.CURRENTYEAR
;
Quit;

%put _all_;

Re: Appending dated tables

Kurt_Bremser — Tue, 27 Feb 2018 10:18:50 GMT

Use a datastep to create the list of variables:

%let currentmonth=201601;
%let previousyearmonth=201501;

data _null_;
length string $1000;
cur_month = input("&previousyearmonth.01",yymmdd8.);
do until (cur_month > input("&currentmonth.01",yymmdd8.));
  string = catx(' ',trim(string),'X_' !! put(cur_month,yymmn6.));
  cur_month = intnx('month',cur_month,1,'b');
end;
call symput('mydatasets',trim(string));
run;

data want;
set &mydatasets.;
run;

Re: Appending dated tables

Perreby — Tue, 27 Feb 2018 10:25:00 GMT

@RW9 wrote:
Why are the data stored like that? From a storage point of view having multiple tables with similar data is inefficient - you have the extra overhead not only of the header block, but a read/write on each. From a programming point of view it is inefficient - as you are finding now, trying to code with such a setup is a nightmare, creating masses of messy macro code. Imagine how much simpler life would be if you stored all that data in one big dataset, with a column (you know those parts of the datasets used to capture data) for date, then your code would be as simple as:
data want;
  set have;
  where date between today() and intnx('month',today(),-1);
run;
Its your data modelling causing your issues - and most likely issues across the board.

Sadly space and processing power is a problem.

We are talking about 100's of millions of records. So having it all in one big table makes it near impossible to statistical analysis on the data on a monthly basis let alone even more detailed disaggregated analysis on region, product, or day level

So its broken up into smaller pieces (i.e monthly data).

But im busy developing an editing system to flag outlying data so need a proper time series (based on user inputs) and this lead me to this.