Selecting Observations from One Table Not In Another

[jcis7]

G'day.

I have two tables, Table A and Table B.

Table A doesn't have any duplicate idcodes.  Neither does Table B.

 Both tables have the same variable names and types.

 I  need to extract only the information from Table B that wasn't in Table A based on idcode to create Table C.  

The following variables are character:  idcode, Name and Status. 

The following variables are numeric:  Enrollment and utd.

TableA

idcode   Name               Status   Enrollment  utd 

12345     BusyBees        Y         100              89

12342     Little Sprouts   Y          23               22

12347     Generations    Y           5                5

TableB

idcode    Name             Status   Enrollment utd

12342     Little Sprouts   Y          23                22

12347     Generations    Y           5                 5

12341     Kidz First         Y          15               11

12231     BrightHorizon  Y          44               32

I need to create:

TableC

idcode    Name             Status   Enrollment utd

12341     Kidz First         Y          15               11

12231     BrightHorizon  Y          44               32

Any help you can give is appreciated!  Thanks!

[Cynthia_sas]

Hi, My tendency would be to use a MERGE, although you could use an SQL join too.

I like the merge approach because I could get all the matches and non-matches with one pass through the data.

cynthia

[jcis7]

Thank you everyone!

[novinosrin · Accepted solution]

data t1;
input idcode   Name  & $20.             Status  $ Enrollment  utd ;
datalines;
12345     BusyBees        Y         100              89
12342     Little Sprouts   Y          23               22
12347     Generations    Y           5                5
;
 

 

 data t2;
 input idcode    Name  & $20.           Status  $  Enrollment utd;
 datalines;
12342     Little Sprouts   Y          23                22
12347     Generations    Y           5                 5
12341     Kidz First         Y          15               11
12231     BrightHorizon  Y          44               32
;

proc sql;
create table want as
select *
from t2
where idcode not in (select idcode from t1);
quit;

[ballardw]

Assuming you description is correct then something like this is one way:

proc sql;
   create table want as 
   select * from tableb
   except 
   select * from tablea
   ;
quit;

Note that if there are variables in only one of the tables that aren't in the other or if the type is different than this will not work.

[jcis7]

Appreciate your help. I got the following when submitting the code. I checked column 6 in both tables and they're both character, 1 length. Thank you.


WARNING: A table has been extended with null columns to perform the EXCEPT set operation.
ERROR: Column 6 from the first contributor of EXCEPT is not the same type as its counterpart from
the second.
ERROR: Numeric expression requires a numeric format.
ERROR: Column 8 from the first contributor of EXCEPT is not the same type as its counterpart from
the second.
ERROR: Numeric expression requires a numeric format.
ERROR: Column 16 from the first contributor of EXCEPT is not the same type as its counterpart from
the second.
ERROR: Column 52 from the first contributor of EXCEPT is not the same type as its counterpart from
the second.
ERROR: Column 53 from the first contributor of EXCEPT is not the same type as its counterpart from
the second.
ERROR: Numeric expression requires a numeric format.
ERROR: Column 57 from the first contributor of EXCEPT is not the same type as its counterpart from
the second.
ERROR: Column 58 from the first contributor of EXCEPT is not the same type as its counterpart from
the second.
ERROR: Numeric expression requires a numeric format.
ERROR: Column 59 from the first contributor of EXCEPT is not the same type as its counterpart from
the second.
ERROR: Column 60 from the first contributor of EXCEPT is not the same type as its counterpart from
the second.
ERROR: Numeric expression requires a numeric format.
ERROR: Column 66 from the first contributor of EXCEPT is not the same type as its counterpart from
the second.
103 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.03 seconds

cpu time 0.01 seconds

[ballardw]

---

@jcis7 wrote:
Appreciate your help. I got the following when submitting the code. I checked column 6 in both tables and they're both character, 1 length. Thank you.


WARNING: A table has been extended with null columns to perform the EXCEPT set operation.
ERROR: Column 6 from the first contributor of EXCEPT is not the same type as its counterpart from
the second.
ERROR: Numeric expression requires a numeric format.

---

And HOW did you check that the column 6 matched? Did you use the names of your data sets? Where there is a message about "extended with null" columns one of the sets has more variables than the other. Notice that I did mention the variables had to match...

Note that this works where we have known data:

data t1;
input idcode   Name  & $20.             Status  $ Enrollment  utd ;
datalines;
12345     BusyBees        Y         100              89
12342     Little Sprouts   Y          23               22
12347     Generations    Y           5                5
;

 data t2;
 input idcode    Name  & $20.           Status  $  Enrollment utd;
 datalines;
12342     Little Sprouts   Y          23                22
12347     Generations    Y           5                 5
12341     Kidz First         Y          15               11
12231     BrightHorizon  Y          44               32
;

proc sql;
   create table want as
   select * from t2
   except
   select * from t1
   ;
quit;

[jcis7]

I see. Thank you.