topic Re: Selecting Observations from One Table Not In Another in SAS Procedures

Selecting Observations from One Table Not In Another

jcis7 — Wed, 25 Apr 2018 21:39:13 GMT

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!

Re: Selecting Observations from One Table Not In Another

Cynthia_sas — Wed, 25 Apr 2018 22:17:03 GMT

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

Re: Selecting Observations from One Table Not In Another

novinosrin — Wed, 25 Apr 2018 22:21:47 GMT

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;

Re: Selecting Observations from One Table Not In Another

ballardw — Wed, 25 Apr 2018 22:39:22 GMT

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.

Re: Selecting Observations from One Table Not In Another

jcis7 — Wed, 25 Apr 2018 23:07:18 GMT

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

Re: Selecting Observations from One Table Not In Another

jcis7 — Wed, 25 Apr 2018 23:09:18 GMT

Thank you everyone!

Re: Selecting Observations from One Table Not In Another

ballardw — Wed, 25 Apr 2018 23:39:57 GMT

@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;

Re: Selecting Observations from One Table Not In Another

jcis7 — Wed, 25 Apr 2018 23:46:18 GMT

I see. Thank you.