Help using Base SAS procedures

proc timeseries

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

proc timeseries

I have a raw daily dataset with prices for stocks. I wanted to transform this to a monthly dataset using proc timeseries. It seems to work fine and is very fast if you use an appropiate index. Since dealing with raw data you expect some messy stuff, so I need a little control over what the procedure is doing. Here I don't find the appropriate options(if they exists).

What I want to do:

Get a monthly timeseries of the last price of the month.

proc timeseries DATA=DAILY_ out=MONTHLY_;

BY STOCKID;

ID Date  INTERVAL=Month accumulate=Last ALIGN=End;

var Price;

run;

 

This produces a nice monthly dataset where Date is now the last day of the month. What is missing: I would need to know the exact price date it used. In the options I used the "last", which does what the description tells you, but for example I'd rather set a price to missing if the last price used is older than 5 days away from the last day of the month.

Anyone?


Accepted Solutions
Solution
‎05-03-2016 04:37 AM
Respected Advisor
Posts: 4,663

Re: proc timeseries

I think the level of control that you need requires datastep programming. The following works (simulated data with some missing months):

 

data daily;
call streamInit(872);
do stockid = "A", "B";
    price = 100;
    do date = '01JAN2014'd to '30JUN2015'd;
        if rand("BERNOULLI", 0.15) then output;
        price + round(5 * rand("NORMAL"), 0.01);
        end;
    end;
format date yymmdd10.;
run;

data monthly0 / view=monthly0;
set daily;
monthEnd = intnx("MONTH", date, 0, "END");
format monthEnd yymmdd10.;
run;

data monthly;
retain prevMonth;
set monthly0; by stockid monthEnd;
if first.stockid then prevMonth = '31DEC2100'd;
if last.monthEnd then do;
    call missing(monthlyPrice);
    month = intnx("MONTH", prevMonth, 1, "END");
    do while(month < monthEnd);
        output;
        month = intnx("MONTH", month, 1, "END");
        end;
    if intck("DAY", date, monthEnd) < 5 then monthlyPrice = price;
    month = monthEnd;
    output;
    prevMonth = monthEnd;
    end;
format month yymmdd10.;
drop monthEnd prevMonth;
run;
PG

View solution in original post


All Replies
Super User
Posts: 17,962

Re: proc timeseries

Proc Expand will give you more options. 

 

Or if all else fails a good old data step Smiley Happy

Contributor
Posts: 29

Re: proc timeseries

I tried proc expand but the results were not understandable. And I wasn't able to stop expand from calculating something on the prices. The data step I will try next, so that would involve by grp-processing etc. Smiley Frustrated

Super User
Posts: 17,962

Re: proc timeseries


niemannk wrote:

I tried proc expand but the results were not understandable. 


BY group processing is pretty straightforward.

 

You can post yoru proc expand that wasn't understandable as well. 

Contributor
Posts: 29

Re: proc timeseries

I think I found the solution using proc expand.

 

I used:

 

from=weekday to=month ALIGN=END OBSERVED=ENDING method=STEP;
	id date;
	by Infocode;

I also have to get rid of prices with missing values. My data has for some stocks complete months missing, the expand procedure "fills up" these months on the output data set. However it copies the last found price for these months. So in the var statement of the expand procedure I added  a copy of the date variable. In a necessary following data step you then could implement the control by comparing the end-of-month date with the copy of the original date.

Super User
Posts: 10,550

Re: proc timeseries

I don't have access to the fancy ETS procedures but can get around some of these types of summaries.

proc summary data=have nway;
   class stockid date;
   var date;
   format date monyy7.;
   output out=monthsummary (drop= _:) max = MaxDate maxId(date(price))=EndMonthPrice;
run; 

The variable MAXDATE in the output will have the monyy format by default but if you change it to MMDDYY it will be the last date WITH a value for price. What you do next is up to you.

 

Solution
‎05-03-2016 04:37 AM
Respected Advisor
Posts: 4,663

Re: proc timeseries

I think the level of control that you need requires datastep programming. The following works (simulated data with some missing months):

 

data daily;
call streamInit(872);
do stockid = "A", "B";
    price = 100;
    do date = '01JAN2014'd to '30JUN2015'd;
        if rand("BERNOULLI", 0.15) then output;
        price + round(5 * rand("NORMAL"), 0.01);
        end;
    end;
format date yymmdd10.;
run;

data monthly0 / view=monthly0;
set daily;
monthEnd = intnx("MONTH", date, 0, "END");
format monthEnd yymmdd10.;
run;

data monthly;
retain prevMonth;
set monthly0; by stockid monthEnd;
if first.stockid then prevMonth = '31DEC2100'd;
if last.monthEnd then do;
    call missing(monthlyPrice);
    month = intnx("MONTH", prevMonth, 1, "END");
    do while(month < monthEnd);
        output;
        month = intnx("MONTH", month, 1, "END");
        end;
    if intck("DAY", date, monthEnd) < 5 then monthlyPrice = price;
    month = monthEnd;
    output;
    prevMonth = monthEnd;
    end;
format month yymmdd10.;
drop monthEnd prevMonth;
run;
PG
Contributor
Posts: 29

Re: proc timeseries

Excellent responses! The proc summary with the maxID() statement was unknow to me. And the data step code is just nice! I will use the data step approach. Note that in both cases you need to make sure that your messy raw data has no missing data in the price variable. I added a where statement in the view definition.

 

It took around 10 min. on a PC to process a 60 GB (messy) input file.

 

Sorry for the late repley.

☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 414 views
  • 0 likes
  • 4 in conversation