topic Re: Proc SQL in SAS Programming

Proc SQL

SASPhile — Fri, 02 Nov 2012 16:51:45 GMT

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.

Re: Proc SQL

LarryWorley — Fri, 02 Nov 2012 17:12:07 GMT

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

Re: Proc SQL

Reeza — Fri, 02 Nov 2012 17:15:48 GMT

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;

Re: Proc SQL

SASPhile — Fri, 02 Nov 2012 17:18:57 GMT

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

Re: Proc SQL

LarryWorley — Fri, 02 Nov 2012 17:27:28 GMT

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 ;

Re: Proc SQL

art297 — Fri, 02 Nov 2012 18:27:48 GMT

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;

Re: Proc SQL

SASPhile — Fri, 02 Nov 2012 19:54:15 GMT

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;

Re: Proc SQL

Reeza — Fri, 02 Nov 2012 19:58:07 GMT

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.

Re: Proc SQL

AkilanR — Fri, 02 Nov 2012 20:47:29 GMT

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;