BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jcis7
Pyrite | Level 9

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!

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
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

7 REPLIES 7
Cynthia_sas
SAS Super FREQ
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
Pyrite | Level 9
Thank you everyone!


novinosrin
Tourmaline | Level 20
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
Super User

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
Pyrite | Level 9
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
Super User

@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
Pyrite | Level 9
I see. Thank you.


SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 1797 views
  • 1 like
  • 4 in conversation