- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
suppose that I have the following data:
stock | date | price |
---|---|---|
A | jan2007 | 4 |
A | feb2007 | 7 |
A | mar2007 | 5 |
A | apr2007 | 2 |
A | mai2007 | 9 |
By now I have a code for calculating the return bw the last and first dates (mai2007 and jan2007), but is there a way where I can calculate the return bw any 2 dates using macros like this I can quickly do the calculation for any period that I want?
Thank you!
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Just add a where into the datastep:
%macro tmp (d1, d2);
proc sort data=ind_sect;
by stock date;
run;
data ind_per;
set ind_sect;
where "&d1."d <= input("01"||strip(date),date9.) <= "&d2."d;
by stock;
retain FirstPrice;
if first.stock
then FirstPrice = price;
if last.stock
then do;
return = (price - FirstPrice)/FirstPrice;
output;
end;
run;
%mend;
%temp (01JAN2007,01MAY2007);
/* Note, not tested, but should be near enough */
You could of course do it in one SQL step also:
%let d1=01JAN2007;
%let d2=01MAY2007;
proc sql;
create table WANT as
select (PRICE - min(PRICE)) / min(PRICE) as RESULT
from (select * from HAVE where "&d1."d <= input("01"||strip(date),date9.) <= "&d2."d);
quit;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Please clarify what kind of code you have, what kind of output you need. Is this for single id, or a statistical call?
How would you chose which dates you would like to base you calculation on?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi Linus,
the code that I have:
proc sort data=ind_sect;
by stock date;
run;
data ind_per;
set ind_sect;
by stock;
retain FirstPrice;
if first.stock
then FirstPrice = price;
if last.stock
then do;
return = (price - FirstPrice)/FirstPrice;
output;
end;
run;
In this code I can calculate the return bw the last and the first prices, but I would like to transform this code into a macro which will enable me to calculate the return bw any 2 dates.
thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Just add a where into the datastep:
%macro tmp (d1, d2);
proc sort data=ind_sect;
by stock date;
run;
data ind_per;
set ind_sect;
where "&d1."d <= input("01"||strip(date),date9.) <= "&d2."d;
by stock;
retain FirstPrice;
if first.stock
then FirstPrice = price;
if last.stock
then do;
return = (price - FirstPrice)/FirstPrice;
output;
end;
run;
%mend;
%temp (01JAN2007,01MAY2007);
/* Note, not tested, but should be near enough */
You could of course do it in one SQL step also:
%let d1=01JAN2007;
%let d2=01MAY2007;
proc sql;
create table WANT as
select (PRICE - min(PRICE)) / min(PRICE) as RESULT
from (select * from HAVE where "&d1."d <= input("01"||strip(date),date9.) <= "&d2."d);
quit;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi RW9,
thanks for creating the code.
But I have some problem with it, the thing is that my actual dates are monthly and in format mmyy7. , and when I tried to run your code I got an error message...
Tried to mend on my own but to no surprise it didn't work either...
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi RW9,
When I run your code I get an error message that the function STRIP should have a character argument.
I guess that instead of STRIP I should use a function which can take dates, could you please hint which one it is?
Thanks!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Just change the where which I suggested adding in:
From
where "&d1."d <= input("01"||strip(date),date9.) <= "&d2."d;
to
where "&d1."d <= date <= "&d2."d;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Redid it, but now the price, last price, return columns are empty for all of the rows
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
OMG it actually worked!!!
its just that in my actual data the variable price is called "prc" and since I used the cide with "price" I was getting empty columns because the variable "price" doesn't exist in my actual data :smileylaugh:
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Similar idea to what @RW9 has offered, but tested
data have;
input stock:$8. date:monyy7. price;
cards;
A jan2007 4
A feb2007 7
A mar2007 5
A apr2007 2
A may2007 9
;
%macro retn
(
stock=
,dt1=
,dt2=
)
;
proc sql;
select (b.price-a.price)/a.price as return format=percent8.2 from have (where = (stock ="%upcase(&stock.)" and date=input("&dt1.",monyy7.))) a,
have (where = (stock ="%upcase(&stock.)" and date=input("&dt2.",monyy7.)))b
;
quit;
%mend;
%retn(stock=a, dt1=jan2007, dt2=may2007)
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi Hai,
ran the code and it worked fine.
In my data I actually have many tickers, so is it possible to make the macro such that it will calculate the return for all the tickers and not just the one ticker which is input into the macro?
thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I can't say I know exactly what you are expecting, try this:
data have;
input stock:$8. date:monyy7. price;
cards;
A jan2007 4
A feb2007 7
A mar2007 5
A apr2007 2
A may2007 9
B jan2007 5
B feb2007 2
B mar2007 3
B apr2007 2
B may2007 9
;
%macro retn
(
dt1=
,dt2=
)
;
proc sql;
select a.stock, (b.price-a.price)/a.price as return format=percent8.2 from have (where = (date=input("&dt1.",monyy7.))) a,
have (where = (date=input("&dt2.",monyy7.)))b
where a.stock=b.stock;
quit;
%mend;
%retn(dt1=feb2007, dt2=may2007)
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
It works perfectly on your code with your data, and that's exactly what I am trying to get, the return for each company.
But for some reason when I run it on my data I get an empty result, still trying to figure out why...
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
My guess is its an issue with the date variable and possibly the comparison.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Dates formatted as MMYY7. still contain day of the month information. Depending on how you get to your current value you may be comparing your date value of say 3Feb2007 with an EQUAL to 1Feb2007.