topic Re: keep only 1 line in SAS Programming

keep only 1 line

deleted_user — Mon, 27 Apr 2009 10:18:26 GMT

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 ?

Re: keep only 1 line

FredrikE — Mon, 27 Apr 2009 10:26:28 GMT

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

//Fredrik

Re: keep only 1 line

DanielSantos — Mon, 27 Apr 2009 10:53:04 GMT

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.

Re: keep only 1 line

sbb — Mon, 27 Apr 2009 13:07:07 GMT

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.

Re: keep only 1 line

data_null__ — Mon, 27 Apr 2009 13:38:21 GMT

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=_:) idgroup(max(date) out(date var:)=);
run;
proc print;
run;
[/pre]

Re: keep only 1 line

DanielSantos — Tue, 28 Apr 2009 16:39:27 GMT

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.