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

[ilikesas]

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!

[LinusH]

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?

[ilikesas]

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

[RW9 · Accepted solution]

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;

[ilikesas]

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

[ilikesas]

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!

[RW9]

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;

[ilikesas]

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

[ilikesas]

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: 

[Haikuo]

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)

[ilikesas]

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

[Haikuo]

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)

[ilikesas]

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

[Reeza]

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

[ballardw]

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.