topic Re: Subsetting Dataset using Proc sql in SAS Programming

Subsetting Dataset using Proc sql

AmirSari — Mon, 13 Aug 2018 22:32:29 GMT

Hi all,

I am trying to subset a dataset based on conditions that are true in another dataset. Basically, I have two datasets, say test_1 and test_2. The dataset test_1 also includes all observations in test_2. I want to drop the test_2 observations from the test_1 dataset. I tried to do that using proc sql but had no success so far.

Here is what I have.

proc sql;

create table want as select *

from test_2

where YEAR not in (select YEAR from test_1) and ID not in (select ID from test_1);
quit;

I am not sure what the problem is but I cannot get the sql to apply both conditions at the same time.

Thanks for your help,

Re: Subsetting Dataset using Proc sql

Patrick — Mon, 13 Aug 2018 22:28:48 GMT

@AmirSari

What is not working? What are you trying to achieve here?

Your where clause will always be true so you won't get any rows returned. What you're doing here: You lookup if a value exists in the same table - that will always be true for any variable as you will always find at least one match (the variable matching with itself from the same row).

Re: Subsetting Dataset using Proc sql

Kurt_Bremser — Mon, 13 Aug 2018 22:29:07 GMT

You only use dataset test_1, which will naturally exclude itself completely.

Re: Subsetting Dataset using Proc sql

AmirSari — Mon, 13 Aug 2018 22:34:25 GMT

I want to create a dataset from test_2. I edited my post. That was my bad.

Re: Subsetting Dataset using Proc sql

AmirSari — Mon, 13 Aug 2018 22:35:57 GMT

I want to to create a dataset from test_2 where the observations that satisfy the two conditions are excluded.

Re: Subsetting Dataset using Proc sql

ballardw — Mon, 13 Aug 2018 22:39:03 GMT

If the variables in test_1 and test_2 are the same and you actually want to exclude the records that are in test_2 from test_1 then perhaps:

proc sql;
   create table want as
   select * from test_1
   except 
   select * from test_2
   ;
quit;

However if the records are not actually identical, different variables for instance, this wouldn't quite work.

In which case you can use except with a subset of variables to identify the records you want from test_1 and then join back with test_1 to get the other variables.

Re: Subsetting Dataset using Proc sql

Ksharp — Tue, 14 Aug 2018 13:09:06 GMT

Maybe you need this in the future.

proc sql;

create table want as select *

from test_2

where catx(' ',YEAR,ID) not in (select catx(' ',YEAR,ID) from test_1 );
quit;

Re: Subsetting Dataset using Proc sql

AmirSari — Tue, 14 Aug 2018 17:08:26 GMT

Thanks, @Ksharp!