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;
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.
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.
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;
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.
This?
proc sql;
select sum(purge='p') into :p_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;
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.
thanks a lot. simply superb.
I mistakenly sorted in this order and could not get the answer. got the logic now.
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: