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
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;
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;
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.
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.
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.