topic Re: creating a macro to calculate rate of change bw any 2 periods in SAS Data Management

creating a macro to calculate rate of change bw any 2 periods

ilikesas — Wed, 18 Mar 2015 15:10:54 GMT

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!

Re: creating a macro to calculate rate of change bw any 2 periods

LinusH — Wed, 18 Mar 2015 15:19:09 GMT

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?

Re: creating a macro to calculate rate of change bw any 2 periods

ilikesas — Wed, 18 Mar 2015 15:28:01 GMT

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

Re: creating a macro to calculate rate of change bw any 2 periods

RW9 — Wed, 18 Mar 2015 15:46:51 GMT

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;

Re: creating a macro to calculate rate of change bw any 2 periods

Haikuo — Wed, 18 Mar 2015 21:21:03 GMT

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)

Re: creating a macro to calculate rate of change bw any 2 periods

ilikesas — Thu, 19 Mar 2015 00:48:11 GMT

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

Re: creating a macro to calculate rate of change bw any 2 periods

ilikesas — Thu, 19 Mar 2015 00:51:20 GMT

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

Re: creating a macro to calculate rate of change bw any 2 periods

Haikuo — Thu, 19 Mar 2015 01:04:27 GMT

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)

Re: creating a macro to calculate rate of change bw any 2 periods

ilikesas — Thu, 19 Mar 2015 01:18:50 GMT

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

Re: creating a macro to calculate rate of change bw any 2 periods

Reeza — Thu, 19 Mar 2015 04:21:20 GMT

My guess is its an issue with the date variable and possibly the comparison.

Re: creating a macro to calculate rate of change bw any 2 periods

ballardw — Thu, 19 Mar 2015 15:55:53 GMT

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.

Re: creating a macro to calculate rate of change bw any 2 periods

ilikesas — Sun, 22 Mar 2015 14:55:26 GMT

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!

Re: creating a macro to calculate rate of change bw any 2 periods

ilikesas — Sun, 22 Mar 2015 14:57:33 GMT

Oh I think that I see,

the thing is that my initial data has dates in the ddmmyy10 format which I after transformed into monyy7 (because I wanted to create categories)

Re: creating a macro to calculate rate of change bw any 2 periods

ilikesas — Sun, 22 Mar 2015 14:59:36 GMT

I think that I realized why your code works good on your created data and not for me: my initial dates are in ddmmyy10. format and from them I created date2 which is in monyy7 format, so is it possible to make an accommodation for this? thanks!

Re: creating a macro to calculate rate of change bw any 2 periods

RW9 — Sun, 22 Mar 2015 16:36:07 GMT

Just change the where which I suggested adding in:

From

where "&d1."d <= input("01"||strip(date),date9.) <= "&d2."d;

where "&d1."d <= date <= "&d2."d;

Re: creating a macro to calculate rate of change bw any 2 periods

ilikesas — Sun, 22 Mar 2015 16:45:23 GMT

Redid it, but now the price, last price, return columns are empty for all of the rows

Re: creating a macro to calculate rate of change bw any 2 periods

Haikuo — Sun, 22 Mar 2015 16:59:59 GMT

Well, if there is no other twists, it should be easy to fix by just switching the informat.

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.",ddmmyy10.))) a,

have (where = (date=input("&dt2.",ddmmyy10.)))b

where a.stock=b.stock;

quit;

%mend;

%retn(dt1=01-01-2007, dt2=01-05-2007)

Some other comments: it may out of line as I don't know exactly what you are trying to achieve, but in general it is not the best practice to trim down format like that, as you can see, you may, at some point, lose information and get stuck in the maze you made.

Re: creating a macro to calculate rate of change bw any 2 periods

ilikesas — Sun, 22 Mar 2015 17:23:17 GMT

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:

Re: creating a macro to calculate rate of change bw any 2 periods

ilikesas — Sun, 22 Mar 2015 21:51:29 GMT

Hi Hai,

my idea is exactly the same thing as you did in your have data and your code.

The only difference is that in my original data I have dates in form ddmmyy10. and for each stock there is one end-of-the-month date, but the actual day might be different for each stock: some have it on the 29th, some on the 30th and some on the 31st.

Since I also wanted to do groupings I created date2 in format monyy7. in order to discard the day

I redid your new code but still get 0 observations

Thats the thing that I hate about programming, these little nuances which are hard to find and which stop the whole process!!!

Re: creating a macro to calculate rate of change bw any 2 periods

Haikuo — Mon, 23 Mar 2015 00:18:40 GMT

Your thought of using monyy7. format is one of the options that you can try, Other solutions can include using intnx() to normalize the date before comparison. Give following another try:

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 = (put(date,monyy7.)="%upcase(&dt1.)")) a,

have (where = (put(date,monyy7.)="%upcase(&dt2.)"))b

where a.stock=b.stock;

quit;

%mend;

%retn(dt1=jan2007, dt2=may2007)