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

Dear experts,

 

I have the following information

 

data have;
  infile datalines dlm=',' dsd truncover;
  input ID Date:anydtdte. Returns Delisting_return month year;
  format date date9.;
  datalines;
1,1967-10-28,1.025,,10,1967
1,1967-11-28,1.026,,11,1967
1,1967-12-28,1.027,,12,1967
1,1968-01-28,1.01,,1,1968
1,1968-02-28,1.04,,2,1968
1,1968-03-28,1.001,,3,1968
1,1968-04-28,1.005,,4,1968
1,1968-05-28,1.02,,5,1968
1,1968-06-28,0.02,,6,1968
1,1968-07-28,0.06,,7,1968
1,1968-08-28,0.06,,8,1968
1,1968-09-28,0.07,,9,1968
1,1968-10-28,0.07,,10,1968
1,1968-11-28,0.08,,11,1968
1,1968-12-28,0.01,,12,1968
1,1969-01-28,0.01,,1,1969
1,1969-02-28,0.04,,2,1969
1,1969-03-28,0.001,,3,1969
1,1969-04-28,0.005,,4,1969
; 
run;

 

 

ID    Date                Returns      Delisting return        month         year

1   1967-10-28     1.025                                            10            1967

1  1967-11-28      1.026                                            11            1967

1   1967-12-28     1.027                                            12            1967

1    1968-01-28      1.01                                             1             1968

1    1968-02-28    1.04                                              2              1968

1    1968-03-28     1.001                                           3              1968

1   1968 -04-28     1.005   .                                       4              1968

1   1968-05-28     1.02                                              5              1968

1   1968-06-28      1.02                                             6               1968

1    1968-07-28     1.06                                              7              1968

1    1968-08-28      1.06                                             8             1968

1   1968-09-28       1.07                                             9            1968

1   1968-10-28       1.07                                            10            1968

1    1968-11-28     1.08                                             11            1968

1    1968-12-28     1.01                                             12            1968

1   1969-01-28     1.01                                               1            1969

1   1969-02-28      1.04                                              2            1969

1    1969-03-28     1.001                                            3            1969

1    1969-04-28     1.005                                            4             1969

 

I would like to conduct multiplication operations on rows. The data is a monthly return data. I would like to find yearly returns using monthly returns, so  I need to multiply monthly returns.  For instance starting from the beginning (when i buy this stock on 1967). I want to conduct the operations as follows:  1.025*1.026*1.027...........*1.07 that is multiply the returns from 1967-10-28 to 1968-10-28, to find the annual return for holding the stock for a given period.

 

Could  anyone please help me with the code to do this task in SAS? Please write if you need more information or clarification. All help is appreciated.

 

I am using SAS 9.4

 

Regards

Akarsh  

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

@kiranv_ wrote:

try this . Please be careful with format of your new col

 

proc sql;

create table want as

select year, returns, exp(sum(log(returns))) as newcol

from have

group by year;

quit;


Interesting, @kiranv_, in all my years of using PROC SQL, this never occurred to me as a way to get the product of a column.

--
Paige Miller

View solution in original post

19 REPLIES 19
Akarsh91
Calcite | Level 5
 <code></code>

Dear experts, 

I have the following information. 

 

data have;
  infile datalines dlm=',' dsd truncover;
  input ID Date:anydtdte. Returns Delisting_return month year;
  format date date9.;
  datalines;
1,1967-10-28,1.025,,10,1967
1,1967-11-28,1.026,,11,1967
1,1967-12-28,1.027,,12,1967
1,1968-01-28,1.01,,1,1968
1,1968-02-28,1.04,,2,1968
1,1968-03-28,1.001,,3,1968
1,1968-04-28,1.005,,4,1968
1,1968-05-28,1.02,,5,1968
1,1968-06-28,1.02,,6,1968
1,1968-07-28,1.06,,7,1968
1,1968-08-28,1.06,,8,1968
1,1968-09-28,1.07,,9,1968
1,1968-10-28,1.07,,10,1968
1,1968-11-28,1.08,,11,1968
1,1968-12-28,1.01,,12,1968
1,1969-01-28,1.01,,1,1969
1,1969-02-28,1.04,,2,1969
1,1969-03-28,1.001,,3,1969
1,1969-04-28,1.005,,4,1969
; 
run;

 

ID    Date                Returns      Delisting return        month         year

1   1967-10-28     1.025                                            10            1967

1  1967-11-28      1.026                                            11            1967

1   1967-12-28     1.027                                            12            1967

1    1968-01-28      1.01                                             1             1968

1    1968-02-28    1.04                                              2              1968

1    1968-03-28     1.001                                           3              1968

1   1968 -04-28     1.005   .                                       4              1968

1   1968-05-28     1.02                                              5              1968

1   1968-06-28      1.02                                             6               1968

1    1968-07-28     1.06                                              7              1968

1    1968-08-28      1.06                                             8             1968

1   1968-09-28       1.07                                             9            1968

1   1968-10-28       1.07                                            10            1968

1    1968-11-28     1.08                                             11            1968

1    1968-12-28     1.01                                             12            1968

1   1969-01-28     1.01                                               1            1969

1   1969-02-28      1.04                                              2            1969

1    1969-03-28     1.001                                            3            1969

1    1969-04-28     1.005                                            4             1969

 

I would like to conduct multiplication operations on rows. The data is a monthly return data. I would like to find yearly returns using monthly returns, so  I need to multiply monthly returns.  For instance starting from the beginning (when i buy this stock on 1967). I want to conduct the operations as follows:  1.025*1.026*1.027...........*1.07 that is multiply the returns from 1967-10-28 to 1968-10-28, to find the annual return for holding the stock for a given period.

 

Could  anyone please help me with the code to do this task in SAS? Please write if you need more information or clarification. All help is appreciated.

 

I am using SAS 9.4

 

Regards

Akarsh  

kiranv_
Rhodochrosite | Level 12

something like this

 

proc sql;

create table want as

select id, date, returns, Delisting_return, month, year, sum(returns) as totalperyear

from have

group by year;

quit;

PaigeMiller
Diamond | Level 26

@kiranv_ wrote:

something like this

 

proc sql;

create table want as

select id, date, returns, Delisting_return, month, year, sum(returns) as totalperyear

from have

group by year;

quit;


The question was not to create a sum, it was to create a product of the returns and as far as I know, there is no PROD function in PROC SQL. (Is there a way to compute products of a column in PROC SQL?)

--
Paige Miller
kiranv_
Rhodochrosite | Level 12

you are right @PaigeMiller. I did not read the question properly.

kiranv_
Rhodochrosite | Level 12

try this . Please be careful with format of your new col

 

proc sql;

create table want as

select year, returns, exp(sum(log(returns))) as newcol

from have

group by year;

quit;

PaigeMiller
Diamond | Level 26

@kiranv_ wrote:

try this . Please be careful with format of your new col

 

proc sql;

create table want as

select year, returns, exp(sum(log(returns))) as newcol

from have

group by year;

quit;


Interesting, @kiranv_, in all my years of using PROC SQL, this never occurred to me as a way to get the product of a column.

--
Paige Miller
PaigeMiller
Diamond | Level 26

Well @Akarsh91, that's nice that you have selected my comment as the answer, but my comment was in reply to a solution from @kiranv_, who provided the code that worked. Of course, the PROC EXPAND solution I provided earlier in the thread gives the same answer, and I suspect the other solutions in this thread also give the same answer.

--
Paige Miller
PaigeMiller
Diamond | Level 26

I think the easiest way might be PROC EXPAND

 

UNTESTED CODE

 

proc expand data=have out=want method=none;
    by year;
    convert returns/transformout=(cuprod);
run;
--
Paige Miller
ballardw
Super User

Since your data goes past more than one twelve month period you may need to provide some additional details about the whole process.

For instance what records are in the output? Do you need the monthly cumulative or only the "annual return"? Is something supposed to reset on an anniversary(Month=10 for your example) and do 2 or 3 or 4 annual returns or only the first?

 

You did not explicitly provide any ID other than 1 so one might assume that each ID is treated separately and likely to have a different first date and hence "annual return" period.

 

What output should occur if there are not at least 12 values? Nothing? return to date?

 

 

Akarsh91
Calcite | Level 5
In output, I would like to see the annual return, for the time period mentioned in my original post. My main goal is however to find the product of the rows. On the anniversary (month=10) the stock is supposed to be sold. As to the ID, it refers to the stock I am holding. ID 1 means that I am only holding 1 stock with ID 1.
kiranv_
Rhodochrosite | Level 12

if possible, can you show how your output should be

Akarsh91
Calcite | Level 5

 

In output i would like to see as follows: 

 

Assuming that I am holding my stock from 1967-10-28 to 1968-10-28

 

ID    Date                Returns      Delisting return        month         year      Ann_ret

1   1967-10-28     1.025                                            10            1967        1.527

1  1967-11-28      1.026                                            11            1967         1.527

1   1967-12-28     1.027                                            12            1967         1.527 

1    1968-01-28      1.01                                             1             1968         1.527

1    1968-02-28    1.04                                              2              1968          1.527

1    1968-03-28     1.001                                           3              1968          1.527

1   1968 -04-28     1.005   .                                       4              1968         1.527

1   1968-05-28     1.02                                              5              1968          1.527

1   1968-06-28      1.02                                             6               1968         1.527

1    1968-07-28     1.06                                              7              1968          1.527

1    1968-08-28      1.06                                             8             1968           1.527

1   1968-09-28       1.07                                             9            1968            1.527

1   1968-10-28       1.07                                            10            1968           1.527

 

 

Ann_ret is found by multiplying the monthly returns as 1.025*1.026*1.027*1.01*1.04*1.001*1.005*1.02*1.02*1.06*1.06*1.07*1.07 that is multiplying the values in each rows. Since i wouldnt be holding my stock any longer than 10-1968 ann_ret wont be calculated for after that period. Is there a procedure to compute Ann_ret in the way mentioned above in SAS? 

ballardw
Super User

@Akarsh91 wrote:
In output, I would like to see the annual return, for the time period mentioned in my original post. My main goal is however to find the product of the rows. On the anniversary (month=10) the stock is supposed to be sold. As to the ID, it refers to the stock I am holding. ID 1 means that I am only holding 1 stock with ID 1.

Since your example data does shows that the stock wasn't sold at month = 10 what should we do?

 

And by output example is should look like something like:

ID    Date                Returns      Delisting return        month         year

1   1967-10-28     1.025                                            10            1967

1  1967-11-28      1.026                                            11            1967

1   1967-12-28     1.027                                            12            1967

1    1968-01-28      1.01                                             1             1968

1    1968-02-28    1.04                                              2              1968

1    1968-03-28     1.001                                           3              1968

1   1968 -04-28     1.005   .                                       4              1968

1   1968-05-28     1.02                                              5              1968

1   1968-06-28      1.02                                             6               1968

1    1968-07-28     1.06                                              7              1968

1    1968-08-28      1.06                                             8             1968

1   1968-09-28       1.07                                             9            1968

1   1968-10-28       1.07                                            10            1968

1    1968-11-28     1.08                                             11            1968

1    1968-12-28     1.01                                             12            1968

1   1969-01-28     1.01                                               1            1969

1   1969-02-28      1.04                                              2            1969

1    1969-03-28     1.001                                            3            1969

1    1969-04-28     1.005                                            4             1969

 only with the added column value.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 19 replies
  • 3963 views
  • 5 likes
  • 5 in conversation