turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- Append 5 consecutive data set in a library

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-15-2017 05:02 AM - edited 12-15-2017 05:02 AM

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 much

Accepted Solutions

Solution

12-15-2017
08:48 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to cczzzl

12-15-2017 05:07 AM

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.

All Replies

Solution

12-15-2017
08:48 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to cczzzl

12-15-2017 05:07 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-15-2017 05:14 AM

Have you tried it in R ?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Gunther

12-15-2017 05:56 AM

I am not sure why I would need to?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-15-2017 05:58 AM

R handles data.frames.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Gunther

12-15-2017 06:06 AM

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

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

Maxims of Maximally Efficient SAS Programmers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to KurtBremser

12-15-2017 06:07 AM

It works on my computer.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Gunther

12-15-2017 06:09 AM

Gunther wrote:

It works on my computer.

Don't brag, show the code.

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

Maxims of Maximally Efficient SAS Programmers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to KurtBremser

12-15-2017 06:13 AM

Ok.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Gunther

12-15-2017 06:18 AM

The size of data set is quite large ( up to 4G per data set).

Using R is not very efficient.

Using R is not very efficient.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-15-2017 06:17 AM

Sorry, it should be started with a "t". I missed it.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to cczzzl

12-15-2017 06:32 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to cczzzl

12-15-2017 08:34 AM

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)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Astounding

12-20-2017 12:21 AM

Great!

Thanks a lot!