Computing Compounded Return in SAS

Question

Hi,

I have a dataset of date(monthly), person and return(monthly). I need to calculate the compounded monthly return of the dataset from April Year t to March Year t+1 for each person. For example,

Annual return Person A= April Year 1* May Year 1*......*March Year 2.

Can I know how can I do that in SAS? Do I need an array?

Answer 1

Do you mean your dataset looks like

Date , Person , Return

"01Apr2009"d , A , 1.1

etc

etc

?

/* Then you can do */

data ur_input_data_view / view = ur_input_data_view;

  /* assuming your dataset is stored in ur_input_data */

  set ur_input_data;

  /* obtain the year and month from the date variable */

  year = year(date);

  mth = year(date);

run;

/* you can skip the sort if your data is already sorted by person then date */

proc sort data = ur_input_data;

  by person date;

run; 

data output_data;

  set ur_input_data_view ;

  by person date;

  /* reset annual return to null if looking at a new person or month is april */

  if first.person or mth = 4 then do;

    annual_return = return;

  end;

  retain annual_return;

  annual_return = annual_return * return;

  /* this will output nothing for the persons that don't have a record at march */

  if mth = 3 then output;

run;