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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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