@jeremy4:
To "just check" whether the file has dupes or not, you need to read the file until one dupe is found. Hence, potentially you may need to read the entire file (if the first dupe is in the last record). Since your file (185k obs) is extremely small, you can just read the whole file anyway using proc SORT. If you don't want to write any output data set at this point (I guess that would fall into the category of "just checking"), _NULL_ out the output data set:
data have ;
input accountID Month ;
cards ;
1 201808
2 201808
3 201805
4 201903
5 201907
2 201808
2 201809
;
run ;
proc sort nodupkey data = have out = _null_ ;
by accountID ;
run ;
proc sort nodupkey data = have out = _null_ ;
by accountID month ;
run ;
The SAS log will tell you if you have dupes and how many. For your particular sample, it reports for the first and second sort, respectively:
NOTE: 2 observations with duplicate key values were deleted.
NOTE: 1 observations with duplicate key values were deleted.
Note that "deleted" doesn't mean that the dupes are deleted from the input file. Rather, it means they would be excluded from the output if it were written out (nothing is written out because of OUT=_NULL_ - you're "just checking").
To generate the unduplicated output file you want, just plug the data set names you want instead of _NULL_:
proc sort equals nodupkey data = have out = out_one ;
by accountID ;
run ;
proc sort equals nodupkey data = have out = out_two ;
by accountID month ;
run ;
Now if your input file were extremely large, not to mention also wide (i.e. having many more variables than the accountID and month), "just checking" using proc SORT to read the whole file can prove rather wasteful, especially if the first dupe is located near the beginning of the input file. In this case, checking for dupes using the hash object may be more efficient since (a) you don't need to sort the input file and (b) the step is stopped as soon as duplicate [accountID,Month] key is detected:
data _null_ ;
dcl hash a () ;
a.definekey ("accountID") ;
a.definedone () ;
dcl hash am () ;
am.definekey ("accountID", "month") ;
am.definedone () ;
do _n_ = 1 by 1 until (eof) ;
set have (keep = accountID Month) end = eof ;
if a.check() = 0 then a_dup = 1 ;
else a.add() ;
if am.check() = 0 then am_dup = 1 ;
else am.add() ;
if a_dup and am_dup then do ;
put "NOTE: Dupes both by accountID and [accountID,Month] detected." ;
stop ;
end ;
end ;
if a_dup then do ;
put "NOTE: Dupes by accountID detected." ;
if am_dup then put "NOTE: Dupes by [accountID,Month] detected." ;
end ;
else put "NOTE: No dupes detected." ;
stop ;
run ;
This program can be expanded in a number of ways, for example, to:
- report on the total number of dupes by either key
- generate unduplicated files in both the original key order and sorted order
- generate files containing the eliminated duplicate records in the original key order and sorted order
(Note that doing any of the above requires that the entire input file be read.) There're more nifty things in the same vein the hash object can be used for. If interested, here's a shameless plug for the "hash book" @DonH and I have put together:
https://support.sas.com/en/books/authors/paul-dorfman.html
Kind regards
Paul D.
