BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8

hi All,

How can this be done in sql?

There are two tables A and B. Both have id and score.

Table A:

Id   score

1     200

3     300

5     600

7     500

Table B:

Id   score

1     200

2     400

3     300

We will need to join these tables and send a report saying that a new record has come from table B.

8 REPLIES 8
LarryWorley
Fluorite | Level 6

Can you clarify what you mean by a new record has come from table B? 

SASPhile
Quartz | Level 8

in table b, there is an id 2. this is a new record.

LarryWorley
Fluorite | Level 6


Except operator is succinct way of doing in sql.  Here is example with your data.

data A;
input Id  score ;
datalines ;
1     200
3     300
5     600
7     500
;

data B ;
input Id  score ;
datalines ;
1     200
2     400
3     300
;

proc sql ;
  select *
    from b
   except
   select *
     from a
     ;
quit ;

Reeza
Super User

Why not use a standard data step merge, it works well for this type of data.

data want;

     merge a (in=ina) b(in=inb);

     by id score;

     if ina and not inb then source='A';

else if inb and not ina then source='B';

else if inb and ina then source='AB';

run;

art297
Opal | Level 21

You might be looking for a combination like:

data A;

  input Id   score;

  cards;

1     200

3     300

5     600

7     500

;

data B;

  input Id   score;

  cards;

1     250

2     450

3     350

4     750

proc sql noprint;

  create table want as

    select *,"A" as wherefrom

      from A

  ;

  insert into want

    select *,"B" as wherefrom

       from B as B

         where b.id not in (select id from A)

  ;

  update want as C

    set wherefrom="B",

        score=(select score from B as B

                where c.id=b.id )

      where c.id in (select id from b)

  ;

quit;

SASPhile
Quartz | Level 8

I did not test this..But got to review coalesce function and thought it may work:

Proc sql;

  Select coalesce(a.id,b.id), coalesce(a.score,a.score),

Case when a.id is ne ‘’ then ‘’

Else ‘Table B’

End as source

from a.id  full join b.id

on a.id=b.id;

quit;

Reeza
Super User

A full join when your data gets big isn't efficient. You'll compare every number against every number, so 4*3=12 comparisons for your small tables.

If you have 1000 in one table and 50 in another that's 50K comparisons.

The exists method from SQL is more efficient with the update/insert.

AkilanR
Fluorite | Level 6

Hi,

You can try the below code

Proc sql;

SELECT B.* FROM B LEFT JOIN A

ON A.ID = B.ID

AND A.SCORE = B.SCORE

WHERE A.ID IS NULL

;

quit;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 8 replies
  • 1098 views
  • 1 like
  • 5 in conversation