topic Re: Extracting observations which are not common in a variable present in two datsets. in New SAS User

Extracting observations which are not common in a variable present in two datsets.

hiteshchauhan1 — Fri, 17 May 2019 10:36:02 GMT

So, the question i'm about to ask is something i'm trying for the first time in sas. Below is what i want to achieve:

I have two data-sets which have a variable which is common in both. Now that variable has some observation which are common in both the data-sets but also there are some values which are not common. Now, i want the list of those variables which are present in one and not in other for both the data-sets.

i can't think of any proc sql or any other statement/function that can help me achieve what i want. Please help me with this.

Thanks

Re: Extracting observations which are not common in a variable present in two datsets.

Jagadishkatam — Fri, 17 May 2019 10:45:42 GMT

Could you please provide a sample data

Re: Extracting observations which are not common in a variable present in two datsets.

Kurt_Bremser — Fri, 17 May 2019 10:56:28 GMT

Several options:

data step:

proc sort data=table1;
by var1;
run;

proc sort data=table2;
by var1;
run;

data exclusions;
merge
  table1 (in=t1 keep=var1)
  table2 (in=t2 keep=var1)
;
by var1;
if t1 and not t2;
if last.t1; /* avoids duplicates */
run;

SQL:

proc sql;
create table exclusions as
select distinct table1.var1
from table1
where var1 not in (select distinct table2.var1 from table2)
;
quit;

Depending on the size of table2, you could also consider to create a "yes/no" format from it, and use that while reading table1 to decide if observations are kept.

Or one could create a hash object from table2 (needs approx. the same amount of memory as the format method).

Re: Extracting observations which are not common in a variable present in two datsets.

hiteshchauhan1 — Fri, 17 May 2019 11:18:09 GMT

Thanks @Kurt_Bremser i tried the proc sql one and it worked smoothly. Though, i nees\d a little clarification on this, the output i will get will be observations of var1 from table1 which are not found in table2. Am i getting it right?

Re: Extracting observations which are not common in a variable present in two datsets.

ballardw — Fri, 17 May 2019 14:53:10 GMT

@hiteshchauhan1 wrote:

Thanks @Kurt_Bremser i tried the proc sql one and it worked smoothly. Though, i nees\d a little clarification on this, the output i will get will be observations of var1 from table1 which are not found in table2. Am i getting it right?

Use a UNION operation between two "directional" selections to get both sides. EXCEPT is another way to do the basic selection.

proc sql;
   create table exclusions as
   (select distinct table1.var1
      from table1
   except
   select distinct table2.var1 from table2)

   union

   (select distinct table21.var1
      from table2
   except
   select distinct table1.var1 from table1)
   ;
quit;

Re: Extracting observations which are not common in a variable present in two datsets.

hiteshchauhan1 — Tue, 21 May 2019 08:59:41 GMT

Thanks @ballardw for the help, working flawlessly.