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

how to calculate Price Growth Persistence ratio in SAS

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Meteorite | Level 14

Re: how to calculate Price Growth Persistence ratio in SAS

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.

5 REPLIES 5
Tourmaline | Level 20

Re: how to calculate Price Growth Persistence ratio in SAS

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

Obsidian | Level 7

Re: how to calculate Price Growth Persistence ratio in SAS

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

Meteorite | Level 14

Re: how to calculate Price Growth Persistence ratio in SAS

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.

Tourmaline | Level 20

Re: how to calculate Price Growth Persistence ratio in SAS

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

That's definitely a matter of opinion. 🙂

Obsidian | Level 7

Re: how to calculate Price Growth Persistence ratio in SAS

Thank you very much! Your code works perfectly!

Discussion stats
• 5 replies
• 796 views
• 2 likes
• 3 in conversation