topic Re: how to calculate Price Growth Persistence ratio in SAS in SAS Programming

how to calculate Price Growth Persistence ratio in SAS

skyland1991 — Mon, 06 Jul 2020 01:14:23 GMT

Hi,

I am new to SAS and I have a project where I need to calculate the price growth persistence ratio. it is a little bit complex so I will try my best to be clear. Here are the details:

1. I have 1000 stocks and each stock has 3~10 years of data. I have calculated the annual relative price for each stock.

2. I need to take each of the 10 years ( I will use 10 years as an example, but not every stock has 10 years of data) as a “base year”, count the number of subsequent years that the average relative price is higher than the base year.

3. Sum the 10 counts. The max count is 45, meaning every year was higher than the base year and the minimum is 0, meaning every year was lower than the base year.

4. Divide the sum by the total possible count (which is 45 in this example) to get a ratio.

I am attaching an example that I did in excel. But calculating the ratio for 1000 stocks really got me stumped. Any help would be greatly appreciated. Thanks.

Re: how to calculate Price Growth Persistence ratio in SAS

ChrisNZ — Mon, 06 Jul 2020 05:37:15 GMT

Like this?

data HAVE;
  ID=1;
  D='03mar2010'd; V=.63; output;
  D='03mar2011'd; V=.44; output;
  D='03mar2012'd; V=.13; output;
  D='03mar2013'd; V=.78; output;
  D='03mar2014'd; V=.27; output;
  D='03mar2015'd; V=.05; output;
  D='03mar2016'd; V=.69; output;
  D='03mar2017'd; V=.98; output;
  D='03mar2018'd; V=.72; output;
  D='03mar2019'd; V=.89; output;
  ID=2;
  D='03mar2010'd; V=.0; output;
  D='03mar2011'd; V=.1; output;
  D='03mar2012'd; V=.2; output;
  D='03mar2013'd; V=.3; output;
  D='03mar2014'd; V=.4; output;
  D='03mar2015'd; V=.5; output;
  D='03mar2016'd; V=.6; output;
  D='03mar2017'd; V=.7; output;
  D='03mar2018'd; V=.8; output;
  D='03mar2019'd; V=.9; output;
run;

data WANT;
  array _V[10] _temporary_;
  set HAVE;
  by ID;
  if first.ID then call missing(N,SUM);
  N+1;
  _V[N]=V;
  if last.ID then do;
    do FROM= 1 to N-1;
      do TO= FROM to N;
        SUM + (_V[FROM] < _V[TO]);     
      end;
    end; 
    output;
    putlog N= SUM=;
  end;
run;

N=10 SUM=30
N=10 SUM=45

Re: how to calculate Price Growth Persistence ratio in SAS

s_lassen — Mon, 06 Jul 2020 08:20:34 GMT

I made a couple of changes to the code supplied by @ChrisNZ :

Using a DoW loop (looping with DO UNTIL(LAST.ID)) makes the code simpler
I increased the array length, just in case there were shares with more than 10 years - in which case you will get the number for all the years.
I put the code to calculate count inside the loop that reads the data, simpler and more efficient.
I added the calculation of the possible max and the PGP ratio.

data want;
  array values(100) 8 _temporary_; /* the array is longer, just in case some shares have more than 10 years */
  count=0;
  do years=1 by 1 until(last.ID);
    set have;
    by ID;
    values(years)=V;
    do i=1 to years-1;
      count+(values(i)<values(years));
      end;
    end;
  max=years*(years-1)/2;
  PGP_Ratio=count/max;
  keep ID years count PGP_ratio;
run;

This datastep assumes the input to be sorted by ID and ascending YEAR.

Re: how to calculate Price Growth Persistence ratio in SAS

ChrisNZ — Mon, 06 Jul 2020 08:54:01 GMT

> Using a DoW loop (looping with DO UNTIL(LAST.ID)) makes the code simpler

That's definitely a matter of opinion. 🙂

Re: how to calculate Price Growth Persistence ratio in SAS

skyland1991 — Tue, 07 Jul 2020 01:27:09 GMT

Thank you very much! Your code works perfectly!

Re: how to calculate Price Growth Persistence ratio in SAS

skyland1991 — Tue, 07 Jul 2020 01:40:02 GMT

Thank you for your reply! Although it is not exactly what I wanted but it does help a lot!