topic Re: Append 5 consecutive data set in a library in SAS Programming

Append 5 consecutive data set in a library

cczzzl — Fri, 15 Dec 2017 10:02:42 GMT

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:)

Re: Append 5 consecutive data set in a library

RW9 — Fri, 15 Dec 2017 10:07:30 GMT

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.

Re: Append 5 consecutive data set in a library

Gunther — Fri, 15 Dec 2017 10:14:00 GMT

Have you tried it in R ?

Re: Append 5 consecutive data set in a library

RW9 — Fri, 15 Dec 2017 10:56:09 GMT

I am not sure why I would need to?

Re: Append 5 consecutive data set in a library

Gunther — Fri, 15 Dec 2017 10:58:09 GMT

R handles data.frames.

Re: Append 5 consecutive data set in a library

Kurt_Bremser — Fri, 15 Dec 2017 11:06:07 GMT

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

Re: Append 5 consecutive data set in a library

Gunther — Fri, 15 Dec 2017 11:07:26 GMT

It works on my computer.

Re: Append 5 consecutive data set in a library

Kurt_Bremser — Fri, 15 Dec 2017 11:09:02 GMT

@Gunther wrote:

It works on my computer.

Don't brag, show the code.

Re: Append 5 consecutive data set in a library

Gunther — Fri, 15 Dec 2017 11:13:43 GMT

Ok.

Re: Append 5 consecutive data set in a library

cczzzl — Fri, 15 Dec 2017 11:17:12 GMT

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

Re: Append 5 consecutive data set in a library

cczzzl — Fri, 15 Dec 2017 11:18:25 GMT

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

Re: Append 5 consecutive data set in a library

RW9 — Fri, 15 Dec 2017 11:32:55 GMT

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

Re: Append 5 consecutive data set in a library

Astounding — Fri, 15 Dec 2017 13:34:12 GMT

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)

Re: Append 5 consecutive data set in a library

cczzzl — Wed, 20 Dec 2017 05:21:16 GMT

Great!

Thanks a lot!