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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

7 REPLIES 7
Reeza
Super User

Proc Expand will give you more options. 

 

Or if all else fails a good old data step 🙂

niemannk
Calcite | Level 5

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

Reeza
Super User

@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. 

niemannk
Calcite | Level 5

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.

ballardw
Super User

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.

 

PGStats
Opal | Level 21

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
niemannk
Calcite | Level 5

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.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 7 replies
  • 2999 views
  • 0 likes
  • 4 in conversation