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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.