Help using Base SAS procedures

Nodupkey using a where statement

Accepted Solution Solved
Reply
Regular Contributor
Posts: 240
Accepted Solution

Nodupkey using a where statement

HI

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


Accepted Solutions
Solution
‎04-24-2013 10:24 AM
Super User
Posts: 17,868

Re: Nodupkey using a where statement

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;

View solution in original post


All Replies
Solution
‎04-24-2013 10:24 AM
Super User
Posts: 17,868

Re: Nodupkey using a where statement

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;

Super User
Posts: 5,085

Re: Nodupkey using a where statement

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.

Super User
Posts: 17,868

Re: Nodupkey using a where statement

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.

Super User
Posts: 9,687

Re: Nodupkey using a where statement

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 174 views
  • 6 likes
  • 4 in conversation