OK, it's just de-duping. Then you can replicate the NODUP + BY _ALL_ results by using the MD5 function and a hash object:
proc sql noprint;
/* Make a csv list of PUT(X,rb8) for all x's that are numeric variables */
select cats('put(',name,',rb8.)') into :num_to_rb8 separated by ','
from dictionary.columns
where libname='PROJECT' and memname='MEDICAID_V01_2010' and type='num';
/* Get the total length of a single observation */
select obslen into :concat_len
from dictionary.tables
where libname='PROJECT' and memname='MEDICAID_V01_2010';
quit;
%put &=num_to_rb8 ;
%put &=obslen ;
data want (drop=_:) ;
set PROJECT.MEDICAID_V01_2010;
/* Concatenate all the data into a single string ("message") named _CONCAT */
length _concat $&concat_len ;
_concat=cat(&num_to_rb8,of _character_);
/* Make a "unique" signature for the message */
length _md5 $16;
_md5=md5(_concat);
if _n_=1 then do;
declare hash md5 (hashexp:10);
md5.definekey('_md5');
md5.definedata('_md5');
md5.definedone();
end;
if md5.find()^=0 then do;
output; /*Output first obs for a given signature*/
md5.add();
end;
run;
I do not directly list numeric variables as arguments of the CAT (or CATX) functions, because different numeric values can generate matching _concat values, (in turn generating matching md5 values), destroying the whole point of de-duping here. Consider the two concatenations below, where X ^= Y, but cat(x,x)=cat(x,y):
data _null_;
x=0.1234567890123456;
y=0.1234567890123457;
if x=y then put "X Equals Y" ;
else put "X Does NOT Equal Y";
cat_x_x = cat(x,x);
cat_x_y = cat(x,y);
if cat_x_x=cat_x_y then put "CAT(X,X) DOES Equal CAT(X,Y)";
else put "CAT(X,X) does NOT = CAT(X,Y)";
run;
which generates the log
X Does NOT Equal Y
CAT(X,X) DOES Equal CAT(X,Y)
In other contexts (for instance when using the MD5 function), this is known as a "collision" - where distinct values of the original data generate equivalent results. That's because the CAT family of functions convert the numeric values into text prior to concatenation, which does not always represent the value to the needed precision. You can avoid that by keeping the original numeric "real binary" representation by using:
cat_x_x = cat(put(x,rb8.),put(x,rb8.));
cat_x_y = cat(put(x,rb8.),put(y,rb8.));
If you rerun the modified program the log will say:
X Does NOT Equal Y
CAT(X,X) does NOT = CAT(X,Y)
That's why you see my PROC SQL code generating the macrovar &num_to_rb8.
The usual concern about a collision risk is in using the MD5 function, but that risk is very very .... very low. It is intended to generate distinct values. Citing page 339 of Data Management Solutions Using SAS Hash Table Operations by Paul Dorfman and Don Henderson:
In the worst case scenario, the approximate number of items that need to be hashed to get a 50 percent chance of an MD5 collision is about 2**64≃2E+19. It means that to encounter just 1 collision, the MD5 function has to be executed against 100 quintillion distinct arguments the equal number of times, i.e., approximately 1 trillion times per second for 100 years. The probability of such an event is so infinitesimally negligible that one truly has an enormously greater chance of living through a baseball season where every single pitch is a strike and no batter ever gets on base. (Amusingly, some people who will confidently say that can never, ever happen may believe that an MD5 collision can happen.)
Now if the number of true duplicates in the original data set is low, one could identify the records having duplicate MD5 values, and then confirm they all arise from true duplicate observations. I'm not including such code here.
