Solved
Contributor
Posts: 44

# first.last and last.id

I need the output of    purge='n'  and  record having highest date with purge='p' .

data purged;

input acc purge\$ date ;

datalines;

111  p 234

234  n 1333

1111 p 2345

2234 n 1333

1121 p 2334

2334 n 3233

;

run;

proc sort data=purged;

by   purge date ;

run;

data purgedorder;

set purged;

by  purge  date;* the following code is working fine.

*by date purge; * if my by statement like this,Need logic for this

if purge='n' then output;

else if(last.purge=1 and last.date=1) then output;

run;

proc print;

run;

Accepted Solutions
Solution
‎08-25-2014 12:41 PM
Super User
Posts: 6,785

## Re: first.last and last.id

A little complex, but not terribly lengthy:

data want;

set have;

by date purge end=done;

if purge='n' then output;

else recno=_n_;

retain recno;

if done then do;

set have point=recno;

output;

stop;

end;

drop recno;

run;

This assumes you need just one purged record.  If you need more than one (such as one per date), it can still be done.  But the question looks like you are asking for just one.

All Replies
Valued Guide
Posts: 2,191

## Re: first.last and last.id

Perhaps easier with sql

PROC SQL ;

CREATE TABLE  selection AS

SELECT a.*

FROM purged  A

WHERE a.purge

OR a.date = MAX( a.date )

;

QUIT ;

If course there is always a "but".

What should happen when 2 ACC have the max date?

The sql code above would select both.

Posts: 1,270

## Re: first.last and last.id

proc sql;

create table want as

select * from purged

where purge='n'

union all

select * from purged

where purge='p'

having date=max(date);

quit;

Contributor
Posts: 44

## Re: first.last and last.id

hi,

thanks for the help. please let me know  the logic in datastep only.

by  purge  date;* the following code is working fine.

by date purge; * if my by statement like this,Need logic for this

reason: mistakenly I sorted in the second way and struggled a lot to get the answer.I want to know whether can we get the result or not ,if we sorted in second way.

Posts: 3,852

## Re: first.last and last.id

This?

data purged;
input acc purge\$ date;
datalines;
111  p 234
234  n 1333
1111 p 2345
2234 n 1333
1121 p 2334
2334 n 3233
;
run;
proc sort data=purged;
by purge descending date;
run;
data purgedorder;
set purged;
by  purge;
if purge eq 'n' then output;

else if first.purge then output;

run;
proc print;

run;
Posts: 1,270

## Re: first.last and last.id

proc sql;
select sum(purge='p') into _count from purged;
quit;

proc sort data=purged;
by date purge;
run;

data want(drop=cnt);
set purged;
by date purge;
if purge='p' then cnt+1;
if purge='n' or (purge='p' and cnt=&p_count);
run;

Solution
‎08-25-2014 12:41 PM
Super User
Posts: 6,785

## Re: first.last and last.id

A little complex, but not terribly lengthy:

data want;

set have;

by date purge end=done;

if purge='n' then output;

else recno=_n_;

retain recno;

if done then do;

set have point=recno;

output;

stop;

end;

drop recno;

run;

This assumes you need just one purged record.  If you need more than one (such as one per date), it can still be done.  But the question looks like you are asking for just one.

Contributor
Posts: 44

## Re: first.last and last.id

thanks a lot. simply superb.

I mistakenly sorted in this order and could not get the answer. got the logic now.

Contributor
Posts: 44

## Re: first.last and last.id

hi,

question:  sas has to create a value/invalue statements for the given dataset (cntlin option for dataset2format convertion).based on what, it is creating value/invalue statements or sas creating both value/invalue statements for the given dataset?

Posts: 3,167

## Re: first.last and last.id

Hash is also considered as Data Step implementation:

data purged;

input acc purge\$ date;

datalines;

111  p 234

234  n 1333

1111 p 2345

2234 n 1333

1121 p 2334

2334 n 3233

;

run;

data want;

if _n_=1 then

do;

declare hash h(ordered: 'a');

h.definekey('date');

h.definedata('purge','acc', 'date');

h.definedone();

declare hiter hi('h');

end;

set purged end=last;

if purge='n' then

output;

else if purge='p' then

rc=h.replace();

if last then

do;

rc=hi.last();

output;

end;

drop rc;

run;

Haikuo

🔒 This topic is solved and locked.