Append 5 consecutive data set in a library

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Append 5 consecutive data set in a library

[ Edited ]

Hi,

 

I have a folder which contains daily trading data for each trading day. 

These daily data are named based on their corresponding trading days. (e.g. 20171201.sas7bat)

I am trying to get a new data set which combine 5 consecutive daily trading data according to a specified signal date.

What I did is firstly to get next 4 weekdays starting from signal date, then to set these data set together in data step.

But the problem is that some weekdays are actually holidays, so no trading on these days.

In this case, what I get maybe  a data set with only 4-day trading data.

Any ideas to correct this?

Thank you very muchSmiley Happy


Accepted Solutions
Solution
‎12-15-2017 08:48 AM
Super User
Super User
Posts: 9,402

Re: Append 5 consecutive data set in a library

Your datasets are not named 20171201, that is not valid, needs to start with an _ or letter.

 

What you can do is, using the sashelp.vtables sort that descending, then take first 5 obs, e.g.:

proc sort data=sashelp.vtable out=inter;
  by memname;
  where libname="YOURLIB";
run;

data inter;
  set inter;
  length klist $2000;
retain klist; if _n_ le 5 then klist=catx(" ",klist,memname); if _n_=5 then call syputx('klist',klist); run; data want; set &klist.; run;

So order the data to get latest 5 files first, then use a retained list of datasets up to 5, and on 5 create macro variable to use in the set statement.

View solution in original post


All Replies
Solution
‎12-15-2017 08:48 AM
Super User
Super User
Posts: 9,402

Re: Append 5 consecutive data set in a library

Your datasets are not named 20171201, that is not valid, needs to start with an _ or letter.

 

What you can do is, using the sashelp.vtables sort that descending, then take first 5 obs, e.g.:

proc sort data=sashelp.vtable out=inter;
  by memname;
  where libname="YOURLIB";
run;

data inter;
  set inter;
  length klist $2000;
retain klist; if _n_ le 5 then klist=catx(" ",klist,memname); if _n_=5 then call syputx('klist',klist); run; data want; set &klist.; run;

So order the data to get latest 5 files first, then use a retained list of datasets up to 5, and on 5 create macro variable to use in the set statement.

Occasional Contributor
Posts: 10

Re: Append 5 consecutive data set in a library

Have you tried it in R ?

Super User
Super User
Posts: 9,402

Re: Append 5 consecutive data set in a library

I am not sure why I would need to?

Occasional Contributor
Posts: 10

Re: Append 5 consecutive data set in a library

R handles data.frames.

Super User
Posts: 9,880

Re: Append 5 consecutive data set in a library

You should post a R solution for the OP's initial question to illustrate your hint.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 10

Re: Append 5 consecutive data set in a library

Posted in reply to KurtBremser

It works on my computer.

Super User
Posts: 9,880

Re: Append 5 consecutive data set in a library


Gunther wrote:

It works on my computer.


Don't brag, show the code.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 10

Re: Append 5 consecutive data set in a library

Posted in reply to KurtBremser

Ok.

Occasional Contributor
Posts: 10

Re: Append 5 consecutive data set in a library

The size of data set is quite large ( up to 4G per data set).
Using R is not very efficient.
Occasional Contributor
Posts: 10

Re: Append 5 consecutive data set in a library

Sorry, it should be started with a "t". I missed it.
Super User
Super User
Posts: 9,402

Re: Append 5 consecutive data set in a library

Yes, my code should still work.  Try it and see if you have further comments.

Super User
Posts: 6,629

Re: Append 5 consecutive data set in a library

I think @RW9 is using a good set of tools.  But some changes would be required to let you specify a signal date.  For example:

 

%macro five_days (signal_date=);

 

proc sort data=sashelp.vtable (keep=memname libname) out=inter;

   by descending memname;

   where libname="YOURLIB" and memname <= "t&signal_date";

run;

 

* then add the remaining code;

 

%mend five_days;

 

%five_days (signal_date=20171204)

 

 

 

 

Occasional Contributor
Posts: 10

Re: Append 5 consecutive data set in a library

Posted in reply to Astounding

Great!

Thanks a lot!

☑ This topic is solved.

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

Discussion stats
  • 13 replies
  • 261 views
  • 2 likes
  • 5 in conversation