how to calculate Price Growth Persistence ratio in SAS

[skyland1991]

 Posted 2 seconds ago (0 views)

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.

[skyland1991]

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.

[ChrisNZ]

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

[skyland1991]

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

[s_lassen · Accepted solution]

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.

[ChrisNZ]

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

That's definitely a matter of opinion. 🙂

[skyland1991]

Thank you very much! Your code works perfectly!