I want to remove the duplicate records but the ones that have missing values as follows
Input
1234 | 12 |
1234 | |
1234 | |
1235 | 13 |
1235 | 13 |
1236 | 11 |
1237 | |
1237 | |
1237 | |
1237 | 10 |
1238 | |
1238 |
Desired Output
1234 | 12 |
1235 | 13 |
1236 | 11 |
1237 | 10 |
1238 |
data have; infile cards truncover expandtabs; input id x; cards; 1234 12 1234 1234 1235 13 1235 13 1236 11 1237 1237 1237 1237 10 1238 1238 ; run; proc sort data=have; by id descending x; run; data want; set have; by id; if first.id; run;
data have; infile cards truncover expandtabs; input id x; cards; 1234 12 1234 1234 1235 13 1235 13 1236 11 1237 1237 1237 1237 10 1238 1238 ; run; proc sort data=have; by id descending x; run; data want; set have; by id; if first.id; run;
Use the fact that missing values are inferior to all non-missing but are not included in the evaluation of MIN:
data test;
infile datalines missover;
input a b;
datalines;
1234 12
1234
1234
1235 13
1235 13
1236 11
1237
1237
1237
1237 10
1238
1238
;
proc sql;
select unique *
from test
group by a
having b >= min(b);
quit;
@PGStats : Its not working for the records where 'b' in all the duplicate records is missing
I get the result
a b -------------------------- 1234 12 1235 13 1236 11 1237 10 1238 .
case where a=1238 seems to be handled properly.
BTW, @Ksharp's solution would not return the case 1235 12, which is not a duplicate, if it was present in your data.
OK. If there are other non missing value in a group , which you want keep: data have; infile cards truncover expandtabs; input id x; cards; 1234 12 1234 1234 1235 13 1235 13 1235 12 1235 1236 11 1237 1237 1237 1237 10 1238 1238 ; run; proc sort data=have; by id descending x; run; data want; set have; by id descending x ; if first.x and not missing(x) or first.id; run;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.