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.
Can you clarify what you mean by a new record has come from table B?
in table b, there is an id 2. this is a new record.
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 ;
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;
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;
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;
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.