BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ilikesas
Barite | Level 11

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!

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

19 REPLIES 19
LinusH
Tourmaline | Level 20

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
ilikesas
Barite | Level 11

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Barite | Level 11

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
Barite | Level 11

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Barite | Level 11

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

ilikesas
Barite | Level 11

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
Onyx | Level 15

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)

ilikesas
Barite | Level 11

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
Onyx | Level 15

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
Barite | Level 11

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
Super User

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

ballardw
Super User

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.

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!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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