Help using Base SAS procedures

Selecting Observations from One Table Not In Another

Accepted Solution Solved
Reply
Regular Contributor
Posts: 206
Accepted Solution

Selecting Observations from One Table Not In Another

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!


Accepted Solutions
Solution
‎04-25-2018 07:05 PM
PROC Star
Posts: 1,769

Re: Selecting Observations from One Table Not In Another

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;

View solution in original post


All Replies
SAS Super FREQ
Posts: 9,365

Re: Selecting Observations from One Table Not In Another

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
Regular Contributor
Posts: 206

Re: Selecting Observations from One Table Not In Another

Posted in reply to Cynthia_sas
Thank you everyone!


Solution
‎04-25-2018 07:05 PM
PROC Star
Posts: 1,769

Re: Selecting Observations from One Table Not In Another

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;
Super User
Posts: 13,498

Re: Selecting Observations from One Table Not In Another

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.

 

Regular Contributor
Posts: 206

Re: Selecting Observations from One Table Not In Another

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


Super User
Posts: 13,498

Re: Selecting Observations from One Table Not In Another


@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;
Regular Contributor
Posts: 206

Re: Selecting Observations from One Table Not In Another

I see. Thank you.


☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 261 views
  • 0 likes
  • 4 in conversation