DATA Step, Macro, Functions and more

Appending dated tables

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Appending dated tables

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

 

 


Accepted Solutions
Solution
‎02-28-2018 12:23 AM
Super User
Posts: 10,239

Re: Appending dated tables

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Regular Contributor
Posts: 164

Re: Appending dated tables

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

- Cheers -

Occasional Contributor
Posts: 6

Re: Appending dated tables

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

Super User
Super User
Posts: 9,599

Re: Appending dated tables

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.

Occasional Contributor
Posts: 6

Re: Appending dated tables

 


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.

 

 

 

 

Solution
‎02-28-2018 12:23 AM
Super User
Posts: 10,239

Re: Appending dated tables

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 140 views
  • 2 likes
  • 4 in conversation