topic Re: SAS output in SAS Programming

Get the latest record based on col2 column

JJP1 — Wed, 24 Jun 2020 07:15:49 GMT

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

Re: SAS output

Reeza — Tue, 23 Jun 2020 17:52:34 GMT

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.

Re: SAS output

Shmuel — Tue, 23 Jun 2020 18:59:46 GMT

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;

Re: SAS output

JJP1 — Wed, 24 Jun 2020 05:35:16 GMT

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'

Re: SAS output

andreas_lds — Wed, 24 Jun 2020 05:55:58 GMT

@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

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

Re: SAS output

JJP1 — Wed, 24 Jun 2020 07:18:55 GMT

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)

Re: SAS output

andreas_lds — Wed, 24 Jun 2020 07:24:24 GMT

@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?

Re: SAS output

JJP1 — Wed, 24 Jun 2020 07:29:51 GMT

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

Re: Get the latest record based on col2 column

sustagens — Wed, 24 Jun 2020 07:32:56 GMT

Try

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

Re: Get the latest record based on col2 column

JJP1 — Wed, 24 Jun 2020 07:36:48 GMT

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