topic Re: Remove duplicate records without missing values in SAS Programming

Remove duplicate records without missing values

deega — Wed, 17 Aug 2016 00:42:43 GMT

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

Re: Remove duplicate records without missing values

Ksharp — Wed, 17 Aug 2016 01:14:32 GMT


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;

Re: Remove duplicate records without missing values

PGStats — Wed, 17 Aug 2016 01:30:04 GMT

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;

Re: Remove duplicate records without missing values

deega — Wed, 17 Aug 2016 03:22:52 GMT

@PGStats : Its not working for the records where 'b' in all the duplicate records is missing

Re: Remove duplicate records without missing values

PGStats — Wed, 17 Aug 2016 03:31:30 GMT

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.

Re: Remove duplicate records without missing values

Ksharp — Wed, 17 Aug 2016 10:16:12 GMT

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;