topic retaining the entry with the highest specific variable value within a certain category in SAS Data Management

retaining the entry with the highest specific variable value within a certain category

ilikesas — Mon, 16 Mar 2015 16:20:56 GMT

Hi,

suppose I have the following data of stocks and their prices for different dates:

stock	date	price
A	1/1/2010	2
A	2/2/2010	6
A	3/3/2011	1
B	4/4/2009	8
B	5/5/2013	4
B	6/6/2014	3

What I would like to have is the for each stock its highest price and the date date when it happened (more precisely, the entire corresponding row):

stock	date	highest price
A	2/2/2010	6
B	4/4/2009	8

thank you!

Re: retaining the entry with the highest specific variable value within a certain category

naveen_srini — Mon, 16 Mar 2015 16:29:33 GMT

proc sort data=have out=have1;

by stock descending price;

run;

data have1;

set have1;

by stock;

if first.stock;

run;

Re: retaining the entry with the highest specific variable value within a certain category

ilikesas — Mon, 16 Mar 2015 17:04:39 GMT

Hi naveen,

thanks for the code, its actually quite simple and I probably should have been able to figure it out, but when posing the question I had in mind calculating the max as part of a bigger code.

And what a lucky coincidence!!!

Yesterday you answered my question about lagged returns with the following code:

data ind_lag;

set ind_sect;

by sector;

k=lag(price);

if not first.sector then return = (price - k) / k;

drop k;

run;

On the same discussion, I was already given an answer for calculating the return between any 2 periods, and wanted to add to it the max price (and its date) between these 2 periods, and that's why I asked the present question.

I guess that now I can just merge max price and date table of your code with the return for each stock bw 2 periods.

Thanks again!!

Re: retaining the entry with the highest specific variable value within a certain category

naveen_srini — Mon, 16 Mar 2015 17:13:53 GMT

Anytime, you are most welcome!. Have a nice day