topic Re: What's the best way to see if a dataset has any duplicates? in New SAS User

What's the best way to see if a dataset has any duplicates?

cosmid — Tue, 23 Jan 2024 02:41:20 GMT

Hi,

Is PROC SORT nodupkey or nodup the best way to check for duplicates or there a better way to quickly check to see if a variable has duplicated values in a dataset?

Thanks

Re: What's the best way to see if a dataset has any duplicates?

yabwon — Tue, 23 Jan 2024 07:46:38 GMT

you can use in memory hash table to read data and print "error" on the first duplicate:

data have;
input x $1. @@;
if x ne " ";
cards;
qwertyuiopasdfghjklzxcvbnm1234567890q
;
run;
proc print;
run;


/* test for dups */
data _null_;
  declare hash H();
  H.defineKey("x");
  H.defineDone();

  do until(eof);
    set HAVE end=eof curobs=curobs;
    rc=H.add();
    if rc then 
      do;
        put "ERROR: Duplicate value: " x "detected in observation " curobs;
        stop;
      end;
  end;
stop;
run;

Bart

Re: What's the best way to see if a dataset has any duplicates?

andreas_lds — Tue, 23 Jan 2024 07:58:52 GMT

Another hash-solution (using the data provided by @yabwon 😞

data _null_;
   if 0 then set have;
   declare hash h(dataset: 'have', duplicate: 'e');
   h.defineKey('x');
   h.defineDone();
   stop;
run;

Re: What's the best way to see if a dataset has any duplicates?

yabwon — Tue, 23 Jan 2024 08:10:45 GMT

And cool thing is that it can be easily extended from only single variable check to row duplicates;

data have;
input x $1. @@;
if x ne " ";
y=rank(x);
z=y*10;
cards;
qwertyuiopasdfghjklzxcvbnm1234567890q
;
run;
proc print;
run;

data _null_;
   if 0 then set have;
   declare hash h(dataset: 'have', duplicate: 'e');
   h.defineKey(all:'yes'); /* duplicated rows */
   h.defineDone();
   stop;
run;

Bart

Re: What's the best way to see if a dataset has any duplicates?

LinusH — Tue, 23 Jan 2024 08:11:06 GMT

Define "best".
Apart from the suggested hash techniques, you could also use PROC SQL with HAVING and COUNT.
Or apply a unique index and see the operation succeeeds.

Re: What's the best way to see if a dataset has any duplicates?

cosmid — Tue, 23 Jan 2024 13:57:33 GMT

Hi andreas!

I didn't know there's a DUPLICATE that can be used with HASH.

I have seen a lot programs with the IF statement:
if 0 then set data_set_name;

I always wondered how that statement can execute because I thought the default numeric value for FALSE is also 0? So the 0 here must mean something else?

Re: What's the best way to see if a dataset has any duplicates?

cosmid — Tue, 23 Jan 2024 13:58:13 GMT

I was really hoping for a much shorter solution, lol...but this will definitely help me learn how to use HAS, thanks!

Re: What's the best way to see if a dataset has any duplicates?

cosmid — Tue, 23 Jan 2024 14:02:29 GMT

Hi LinusH,

So, I was hoping for a built-in SAS function that I didn't know of or something like a one line of code. The other solution I found beside PROC SORT was using the FIRST and LAST and compare them. The PROC SORT creates another dataset and the FIRST and LAST involves more coding so I was hoping for a shorter version of some sort. I thought there might be one that exist since checking for duplicates is such a common task.

Re: What's the best way to see if a dataset has any duplicates?

cosmid — Tue, 23 Jan 2024 23:01:19 GMT

I understand the IF 0 now. It's used to set the PDV and skip reading in the observations.
Sorry, I wanted to follow up because I asked about it in an earlier reply and I don't know how to delete that reply. So in case I'll waste more of your time to answer I'll just explain here.
Thanks again for the help!

Re: What's the best way to see if a dataset has any duplicates?

SASKiwi — Wed, 24 Jan 2024 00:39:39 GMT

Personally I find it useful to create macros for common tasks like this. It means you can get your answer with just one statement. It also means the underlying method isn't so important.

%macro Find_Dups ( dataset = 
                  ,byvar   =
                  ,dupvar  = 
                 );

%if &dupvar = %then %let dupvar = &byvar; 

proc sort data = &dataset 
          out = sorted
           ;
  by &byvar;
run;

data dups;
  set sorted;
   by &byvar;
  if not (first.&dupvar and last.&dupvar);
run;

%mend Find_Dups;

Although you will notice that I prefer to create a table with the duplicate rows.

Re: What's the best way to see if a dataset has any duplicates?

cosmid — Sun, 25 Feb 2024 16:18:39 GMT

Thanks for the code! Is there a way for SAS to take parameters at the command line? I'm referring to Linux environment. For example, if I wanted to check if dataset sample.sas7bdat has any duplicate, I could just run the program with command like SAS PROG.SAS sample var
And the program will take the first parameter as the dataset and the 2nd parameter as the BY variable

Re: What's the best way to see if a dataset has any duplicates?

Tom — Sun, 25 Feb 2024 17:07:19 GMT

This thread seems to be devolving into a general discussion.

Much better to post new questions on new threads. You can always include a link to some older topic.

You can use the old -sysparm option.

https://documentation.sas.com/doc/en/mcrolref/3.2/p0ajr6rtdhuhzbn199hhpkak2v8p.htm

Or you can take advantage of the new -set option.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/hostunx/n106qouqj0hfk5n1wgqpw8iovxy2.htm

Re: What's the best way to see if a dataset has any duplicates?

SASKiwi — Sun, 25 Feb 2024 22:06:27 GMT

@cosmid - I suggest you follow @Tom 's advice regarding the SET option which creates environment variables you can read using %SYSGET or SYSGET in your SAS program.