DATA Step, Macro, Functions and more

keep only 1 line

Reply
N/A
Posts: 0

keep only 1 line

I have sas data set as below

id var2 var3 date
001 B 11 20080601
001 B 11 20080709
001 B 11 20090113

I have 3 lines but same id, i would like to keep only the latest date line (in this case is 20090113). How do i keep it ?
Frequent Contributor
Posts: 120

Re: keep only 1 line

proc sort data = DSNAME;
by id date;
if last.id then output;
run;

//Fredrik
Super Contributor
Posts: 474

Re: keep only 1 line

Somethings wrong about the code above.

The correct syntax would be.

proc sort data = yourdata; /* arrange data by id, and descending date */
by id descending date;
run;

proc sort nodupkey; /* keep first id of each group */
by id;
run;

OR:

proc sort data = yourdata; /* arrange data by id, and descending date */
by id descending date;
run;

data yourdata; /* keep first id of each group */
set yourdata;
by id;
if first.id;
run;

both do the same.

Greetings from Portugal.

Daniel Santos at www.cgd.pt.
Super Contributor
Super Contributor
Posts: 3,174

Re: keep only 1 line

The SORT-only execution requires the EQUALS parameter, if not the default for your sort package. And the use of FIRST. or LAST. with regards to OUTPUT delivers the same result.

Scott Barry
SBBWorks, Inc.
Super Contributor
Posts: 474

Re: keep only 1 line

Scott is right.
My system options is set to SORTEQUALS, so if you're not sure about yours, you should specify the EQUALS options for the sort procedure to ensure that the order is preserved.

_data null_'s solution is a great alternative (didn't knew about the idgroup option).

Greetings from Portugal.

Daniel Santos at www.cgd.pt.
Respected Advisor
Posts: 3,777

Re: keep only 1 line

PROC SUMMARY provides nice single step solution to this problem.

[pre]
data have;
input id:$3. var2:$1. var3 date :yymmdd.;
format date date9.;
cards;
001 B 11 20080601
001 B 11 20080709
001 B 11 20090113
002 B 11 20080601
002 a 12 20080709
002 B 11 20060113
;;;;
run;
proc summary data=work.have nway missing;
class id;
output out=need(drop=_Smiley Happy idgroup(max(date) out(date varSmiley Happy=);
run;
proc print;
run;
[/pre]
Ask a Question
Discussion stats
  • 5 replies
  • 146 views
  • 0 likes
  • 5 in conversation