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

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

13 REPLIES 13
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Gunther
Fluorite | Level 6

Have you tried it in R ?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

I am not sure why I would need to?

Gunther
Fluorite | Level 6

R handles data.frames.

Gunther
Fluorite | Level 6

It works on my computer.

cczzzl
Calcite | Level 5
The size of data set is quite large ( up to 4G per data set).
Using R is not very efficient.
cczzzl
Calcite | Level 5
Sorry, it should be started with a "t". I missed it.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Astounding
PROC Star

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)

 

 

 

 

cczzzl
Calcite | Level 5

Great!

Thanks a lot!

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
  • 13 replies
  • 1059 views
  • 2 likes
  • 5 in conversation