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.
I made a couple of changes to the code supplied by @ChrisNZ :
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.
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
Thank you for your reply! Although it is not exactly what I wanted but it does help a lot!
I made a couple of changes to the code supplied by @ChrisNZ :
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.
> Using a DoW loop (looping with DO UNTIL(LAST.ID)) makes the code simpler
That's definitely a matter of opinion. 🙂
Thank you very much! Your code works perfectly!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: