BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Perreby
Fluorite | Level 6

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

5 REPLIES 5
Oligolas
Barite | Level 11

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 -

Perreby
Fluorite | Level 6

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Perreby
Fluorite | Level 6

 


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

 

 

 

 

Kurt_Bremser
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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