Create new dataset depending on another dataset

[Tamino]

Hello,

I have two data sets with patients, which all have an id. The patients_all dataset contains all patients, the patients_exclude dataset contains the patients I want to exclude.

Now I want to create a dataset patients_all_clear which contains all patients except those from the dataset patients_exclude. The patients are identified by their id.

In short: various id from the dataset patients_exclude should no longer appear in the new dataset patients_all_clear.

What is the most clever way to do this?

Thanks a lot!

[PaigeMiller]

Try something like this.

proc sql;
    create table patients_all_clear as select * from patients_all
        where id not in (select distinct id from patients_exclude);
quit;

[Kurt_Bremser · Accepted solution]

Most modern and most efficient method:

data patients_all_clear;
set patients_all;
if _n_ = 1
then do;
  declare hash ex (dataset:"patients_exclude");
  ex.definekey("id");
  ex.definedata("id"); /* not necessary, but reduces memory footprint */
  ex.definedone();
end;
if ex.check() = 0 /* id is found */ then delete;
run;

[Tamino]

I tried:

data patients_all_clear;
     merge	patients_all	(in = in1)
         	patients_exclude    (in = in2);
     by id;
     if in1 and not in2;
run;

Could there be a reason not to do it like this, or is it possible like this?

[Astounding]

This is probably the most common approach, well understand by most SAS programmers.

On the downside:  it requires sorted data sets.  (The other suggestions work on unsorted data.)  It might take slightly longer to run, but that is unlikely to be an issue.

Also note that the final statement could be simplified:

if not in2;

Any patient not found in IN2 must have come from IN1.

[Kurt_Bremser]

If the datasets are already sorted by id, you can do this, and it will perform fine. Be careful which variables are contained in the datasets, because you might overwrite values you want to keep.

The hash method allows to use a lookup table without having to sort any of the datasets, as the sort is performed implicitly in memory when the key table is built.

[Kurt_Bremser]

And I found a (very slight) code improvement:

if ex.check() = 0 then delete;

can be shortened to a subsetting if:

if ex.check() ne 0;