Get the latest record based on col2 column

[JJP1]

Hi All,

i have input data as below with columns as date,COL2,COL3,COL4 and COL5. tried using proc sort along with first and last options but not giving desired output.please help.

21JAN2020 4 XXX MAMA 1232343443
21JAN2020 5 XXX MAMA 1232343443
23JAN2020 10 XXX HAHAHA 1232343443
23JAN2020 11 XXX HAHAHA 1232343443
14FEB2020 19 XXX HAHAHA 1232343443
14FEB2020 21 XXX HAHAHA 1232343443
23MAR2020 30 XXX MUMUMUM 1232343443
23MAR2020 31 XXX MUMUMUM 1232343443
25MAR2020 32 XXX FUFUFUF 1232343443
25MAR2020 33 XXX FUFUFUF 1232343443
25MAR2020 37 XXX FUFUFUF 1232343443
26MAR2020 47 XXX HARHAR 1232343443
26MAR2020 52 XXX HARHAR 1232343443
26MAR2020 56 XXX HARHAR 1232343443
26MAR2020 45 XXX MUMUMUM 1232343443
26MAR2020 46 XXX MUMUMUM 1232343443
26MAR2020 54 XXX MUMUMUM 1232343443
26MAR2020 55 XXX MUMUMUM 1232343443

Expecting output as below 

26MAR2020 56 XXX HARHAR 1232343443 

[Reeza]

What's your logic for extracting that single record?

In theory this would work but likely will not work on your actual data because the logic doesn't generalize to your actual data.

data want;
set have;
by date var2 var3 notsorted;
if last.var3 and var3='HRHRHT';
run;

EDIT: should be last.var3 not last.date.

[Shmuel]

Are COL2 and COL3 the same for all observation of the same date?

Is col2 the same for all observations?

To use first.<var> or lats.<var> you need:

   (1) data most be sorted by <major varibles> & the <var> as the minor one,

         as in next example:

proc sort data=have;
    by date col2 col3;
run;
data want;
   set have;
     by date col2 col3;
         if first.col3;    /* or if last.col3; */
        .....
run;

Finally, what was the logic to select net line as wanted:

26MAR2020	XXX	CCCCCC	123456676

Are you looking for the first observation? the last observation? of a specific date?

If you mean the first observation of the latest date

then you can use next codes:

/* option 1 */
proc sort data=have;
 by descending date /*col2 ? */ col3;
run;
data want;
 set have;
  by descending date col3;
  if _N_ = 1;
run;

/* option 2 */
proc sql noprint;
  create table temp
  as select max(date) as date
                 min(col3) as col3
  from have;
  create table want  
    as select distinct * 
	from have as h,
	        temp as t
	where h.date = t.date and
	         h.col3 = t.col3;
quit;
	

[JJP1]

Actually the problem here is all date values and other column values are same except COL3.

Currently if we sort the data and getting the lastobservation based on last.col4 and fetching col3 value that is nothing but JJJJJJJ. But as per correct scenario that col3 is not the last one and i need to arrange the data so that last value will come as 'CCCCCC'. so that i can take last.col4 

tried multiple ways but i am getting 'JJJJJJJ'

[andreas_lds]

---

@JJP1 wrote:

Actually the problem here is all date values and other column values are same except COL3.

Currently if we sort the data and getting the lastobservation based on last.col4 and fetching col3 value that is nothing but JJJJJJJ. But as per correct scenario that col3 is not the last one and i need to arrange the data so that last value will come as 'CCCCCC'. so that i can take last.col4 

tried multiple ways but i am getting 'JJJJJJJ'

---

I don't know any alphabet in which multiple Cs are sorted after multiple Js. So please

1. post data that is closer to the data you have
2. Describe the logic to be used to select the observation you want.

[JJP1]

Thanks @andreas_lds and @Shmuel  and @Reeza .

Actually i have brought in another column. please have a look now with input data(edited the original post)

[andreas_lds]

---

@JJP1 wrote:

Thanks @andreas_lds and @Shmuel  and @Reeza .

Actually i have brought in another column. please have a look now with input data(edited the original post)

---

Pleaser never ever again edit a post in that way, now all answers already given are completely impossible to understand.

So, you want to select the observation with highest value in col2?

[JJP1]

yes @andreas_lds . sorry . i will ensure this from next time on wards

[sustagens]

Try

proc sql;
create table want as 
select * 
from have
where col2 eq (select max(col2) from have);
quit;

[JJP1]

Thanks @sustagens . i have sorted using proc sort the data based on col2 and got the desired output based on last.col4