topic Re: Nodupkey using a where statement in SAS Procedures

Nodupkey using a where statement

BETO — Wed, 24 Apr 2013 14:21:54 GMT

i have a table that has duplicate entries here is and example.

ID. Lat. Long

001. 0 0

001. 33.4. -112.221

i would want to remove the duplicate that has 0 for lat and long and keep the entry where there is a lat and long.

here is a snippet of what I'm using

proc sort data= table1

out=table1

nodupkey;

where lat <> 0;

by ID;

which works except it also removes some id that's are not dups but have a 0 in the lat and long columns ....thanks again for assistance

Re: Nodupkey using a where statement

Reeza — Wed, 24 Apr 2013 14:24:46 GMT

Sort first descending by lat then use nodupkey. This will push the observations with values before the ones with 0.

proc sort data=have; by id descending lat;

run;

proc sort data=have nodupkey; by id;

run;

Re: Nodupkey using a where statement

Astounding — Wed, 24 Apr 2013 14:43:39 GMT

The possibility of negative values can make this tricky. If you can guarantee that each ID has at most one record with all 0s, here's a way to do it:

proc sort data=have;

by id;

run;

data want;

merge have (where=(lat=0 and long=0))

have (where=(lat ne 0 or long ne 0));

by id;

run;

Good luck.

Re: Nodupkey using a where statement

Reeza — Wed, 24 Apr 2013 14:49:38 GMT

True, but latitude and longitude have very well defined transition points to negative/positive. If I'm dealing with Canadian data for example, I know that all my latitudes will be positive.

For countries that cross boundaries another solution would be required.

Re: Nodupkey using a where statement

Ksharp — Wed, 24 Apr 2013 15:02:37 GMT

How about this:

data have;
input ID   $   Lat       Long ;
cards;
001.    0             0
001.    33.4      -112.221
002.     0             0
;
run;
proc sort data=have;
   by id;
run;
data want;
 set have;
 by id;
 if  lat = 0 and not(first.id and last.id) then delete;
run;

Ksharp