SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Super Contributor
Posts: 441
Accepted Solution

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

Hi,

suppose that I have the following data:

stockdateprice
Ajan20074
Afeb20077
Amar20075
Aapr20072
Amai20079

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
Solution
‎03-18-2015 11:46 AM
Super User
Super User
Posts: 7,970

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

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;

View solution in original post


All Replies
Super User
Posts: 5,431

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

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?

Data never sleeps
Super Contributor
Posts: 441

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

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

Solution
‎03-18-2015 11:46 AM
Super User
Super User
Posts: 7,970

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

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;

Super Contributor
Posts: 441

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

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

Super Contributor
Posts: 441

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

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!

Super User
Super User
Posts: 7,970

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

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;

Super Contributor
Posts: 441

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

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

Super Contributor
Posts: 441

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

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: 

Respected Advisor
Posts: 3,156

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

Similar idea to what @RW9 has offered, but tested Smiley Happy

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)

Super Contributor
Posts: 441

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

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

Respected Advisor
Posts: 3,156

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

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)

Super Contributor
Posts: 441

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

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

Super User
Posts: 19,822

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

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

Super User
Posts: 11,343

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

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 19 replies
  • 966 views
  • 6 likes
  • 6 in conversation