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

Hi I have a panel data of daily share prices , my distribution looks like below . I have 1000 companies daily share price data from 1/1 to 12/31 of every month for each company . I need to compute daily log returns of the share prices but since the companies are continuous the 1st day of company B will take the last day of company A into return calculation. Could someone help me on the SAS code to compute daily returns .

Company Company CodeDateDaily share price

A

1/1/2000
A1/2/2000
A12/31/2000
B1/1/2000
B12/31/2000
1 ACCEPTED SOLUTION

Accepted Solutions
ets_kps
SAS Employee

There is an alternative way to do this that doesn't involve using all this first., last. syntax. Using PROC TIMEDATA, you can directly reference the time series within BY groups.  I feel the syntax is much more intuitive.

Here is full sample in action.

data greene;
   input firm year production cost @@;
date = mdy(1,1,year);
datalines;
1 1955   5.36598   1.14867  1 1960   6.03787   1.45185
1 1965   6.37673   1.52257  1 1970   6.93245   1.76627
2 1955   6.54535   1.35041  2 1960   6.69827   1.71109
2 1965   7.40245   2.09519  2 1970   7.82644   2.39480
3 1955   8.07153   2.94628  3 1960   8.47679   3.25967
3 1965   8.66923   3.47952  3 1970   9.13508   3.71795
4 1955   8.64259   3.56187  4 1960   8.93748   3.93400
4 1965   9.23073   4.11161  4 1970   9.52530   4.35523
5 1955   8.69951   3.50116  5 1960   9.01457   3.68998
5 1965   9.04594   3.76410  5 1970   9.21074   4.05573
6 1955   9.37552   4.29114  6 1960   9.65188   4.59356
6 1965  10.21163   4.93361  6 1970  10.34039   5.25520
;

proc sort data=greene out=greene; by firm year; run;


proc timedata data=greene out=differenced;
by firm;
id date interval=year ;
var cost /dif=(5);
var production /diff=5;
run;

Yours might look like;

PROC TIMEDATA data=returns out=returns2;
by company;

id Date interval=day;

var price /diff=(1);

run;

There are also ways to do this in PROC PANEL

View solution in original post

4 REPLIES 4
1zmm
Quartz | Level 8

Sort your data by company and date.  Then use the SAS-created variables, FIRST.COMPANY and LAST.COMPANY, to define the range of observations that you want to calculate your statistics on daily log returns:

==================================================================================================

    proc sort data=old;

         by company date;

    run;

    data new;

         set old;
         by company date;

         if (first.company eq 1) then do;

             <<code to calculate statistics for first date within a company>>

         end;

         if ((first.company eq 0) and

              (last.company eq 0)) then do;

                 << code to calculate statistics for the second through next-to-last dates within a company>>

         end;

         if (last.company eq 1) then do;

             <<code to calculate statistics for the last date within a company>>

         end;

         output new;

   run;

=====================================================================================

Within the DATA step, you may need to include a RETAIN statement to hold variable values across observations.

ets_kps
SAS Employee

There is an alternative way to do this that doesn't involve using all this first., last. syntax. Using PROC TIMEDATA, you can directly reference the time series within BY groups.  I feel the syntax is much more intuitive.

Here is full sample in action.

data greene;
   input firm year production cost @@;
date = mdy(1,1,year);
datalines;
1 1955   5.36598   1.14867  1 1960   6.03787   1.45185
1 1965   6.37673   1.52257  1 1970   6.93245   1.76627
2 1955   6.54535   1.35041  2 1960   6.69827   1.71109
2 1965   7.40245   2.09519  2 1970   7.82644   2.39480
3 1955   8.07153   2.94628  3 1960   8.47679   3.25967
3 1965   8.66923   3.47952  3 1970   9.13508   3.71795
4 1955   8.64259   3.56187  4 1960   8.93748   3.93400
4 1965   9.23073   4.11161  4 1970   9.52530   4.35523
5 1955   8.69951   3.50116  5 1960   9.01457   3.68998
5 1965   9.04594   3.76410  5 1970   9.21074   4.05573
6 1955   9.37552   4.29114  6 1960   9.65188   4.59356
6 1965  10.21163   4.93361  6 1970  10.34039   5.25520
;

proc sort data=greene out=greene; by firm year; run;


proc timedata data=greene out=differenced;
by firm;
id date interval=year ;
var cost /dif=(5);
var production /diff=5;
run;

Yours might look like;

PROC TIMEDATA data=returns out=returns2;
by company;

id Date interval=day;

var price /diff=(1);

run;

There are also ways to do this in PROC PANEL

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!

What is ANOVA?

ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 2637 views
  • 3 likes
  • 3 in conversation