BookmarkSubscribeRSS Feed
yotsuba88
Quartz | Level 8

I have a date variable from 1926/01/01 to 2013/12/31. And stock returns for monthly.

I want to do is that use 36 month as a rolling window. Use full 36 month's returns to calculate a new variable and merge that variable as the next month's date after 36 month window.

I did use CROSS JOIN to create different loops which contain 36 month data. But I only want to use only stocks which have a complete return history over 36 months, so please tell me how to delete stocks that do not have enough this data.

 

 

8 REPLIES 8
Ksharp
Super User

I think CASE... END clause can do that. Can you post your data and output ?

 

proc sql;

..................

case 

when intck('month',

(  select min(date) from   have where id=a.id and date between intnx('month',a.date,-36,'s') and a.date  )

,a.date,'c') < 36

then .

else 

(select mean(return) from have where id=a.id and date between intnx('month',a.date,-36,'s') and a.date)

end as want_return

from have as a

yotsuba88
Quartz | Level 8

Thank you for your reply. I have tried to upload my sas file but it cannot work. I upload my excel file with just 3 companies since 1985 here. 

 

 

Kurt_Bremser
Super User

Please DO NOT post data in MS Officer files. For many of the contributors here, their organization's firewall will block such files because of security reasons, and the others won't open them because of said security reasons.

Please post example data in a data step with datalines: How to create a data step version of your data AKA generate sample data for forums

 
yotsuba88
Quartz | Level 8

Hi KurtBremser,

 

Thank you so much for your warnings. I am new leaner as well as new member so I will pay attention next time.

 

 

Reeza
Super User

It's probably easier to create a count first of each ticker and then filter out those with the missing values. 

 

You can simulate your data with 5 points and include it directly in the post. 

 

Are you saying any ticker missing data for a specific period should be excluded? Have you looked at the ETS procedures - TIMESERIES and EXPAND? 

Shmuel
Garnet | Level 18

Assume data is sorted by date, assign a new variable for first occurence of a month to 1 otherwize to 0,

then filter those with sum(variable) < 36.

Ksharp
Super User

It is easy if one row correspond to one month.

 

proc import datafile='/folders/myfolders/Result IBM MSFT AAPL 1985 R2.xlsx' out=have dbms=xlsx replace;run;
data have;
 set have;
 by cusip;
 if first.cusip then n=0;
 n+1;
run;
proc sql;
create table want as
select *,
case 
when 
(select count(*) from have where cusip=a.cusip and n between a.n-35 and a.n) lt 36 then .
else 
(select mean(nvalue1) from have where cusip=a.cusip and n between a.n-35 and a.n) lt 36 
end as want_return
from have as a;
quit;
yotsuba88
Quartz | Level 8

It works for me now.  I really appreciate it. 

 

 

 

 

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!

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
  • 8 replies
  • 1495 views
  • 3 likes
  • 5 in conversation