DATA Step, Macro, Functions and more

Proc SQL

Reply
Super Contributor
Posts: 647

Proc SQL

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.

Frequent Contributor
Posts: 129

Re: Proc SQL

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

Super Contributor
Posts: 647

Re: Proc SQL

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

Frequent Contributor
Posts: 129

Re: Proc SQL


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 ;

Super User
Posts: 17,840

Re: Proc SQL

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;

PROC Star
Posts: 7,363

Re: Proc SQL

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;

Super Contributor
Posts: 647

Re: Proc SQL

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;

Super User
Posts: 17,840

Re: Proc SQL

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.

Occasional Contributor
Posts: 17

Re: Proc SQL

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;

Ask a Question
Discussion stats
  • 8 replies
  • 311 views
  • 1 like
  • 5 in conversation