topic Re: Create new dataset depending on another dataset in SAS Programming

Create new dataset depending on another dataset

Tamino — Wed, 26 Jan 2022 14:21:22 GMT

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!

Re: Create new dataset depending on another dataset

PaigeMiller — Wed, 26 Jan 2022 14:29:33 GMT

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;

Re: Create new dataset depending on another dataset

Kurt_Bremser — Wed, 26 Jan 2022 14:41:23 GMT

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;

Re: Create new dataset depending on another dataset

Tamino — Wed, 26 Jan 2022 15:31:58 GMT

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?

Re: Create new dataset depending on another dataset

Astounding — Wed, 26 Jan 2022 15:50:37 GMT

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.

Re: Create new dataset depending on another dataset

Kurt_Bremser — Wed, 26 Jan 2022 17:02:46 GMT

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.

Re: Create new dataset depending on another dataset

Kurt_Bremser — Tue, 15 Feb 2022 14:18:07 GMT

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;