SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Super Contributor
Posts: 413
Accepted Solution

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

Hi,

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

stockdateprice
A1/1/20102
A2/2/20106
A3/3/20111
B4/4/20098
B5/5/20134
B6/6/20143

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):

stockdatehighest price
A2/2/20106
B4/4/20098

thank you!


Accepted Solutions
Solution
‎03-16-2015 12:29 PM
Frequent Contributor
Posts: 115

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

proc sort data=have out=have1;

by stock descending price;

run;

data have1;

set have1;

by stock;

if first.stock;

run;

View solution in original post


All Replies
Solution
‎03-16-2015 12:29 PM
Frequent Contributor
Posts: 115

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

proc sort data=have out=have1;

by stock descending price;

run;

data have1;

set have1;

by stock;

if first.stock;

run;

Super Contributor
Posts: 413

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

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!!

Frequent Contributor
Posts: 115

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

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 278 views
  • 0 likes
  • 2 in conversation