Hi,
I am new to SAS DI and seeking help from experience guys.
I had a requiremnt to merge the two temporary tables coming from extract txn.
There are 6 columns in common which have different data like below.
dataset t1
A B C D E F
apple fruit 23 0 25 0
mango fruit 0 12 0 45
carrot veg 0 52 0 23
tomato veg 3 0 16 0
dataset t2
A B C D E F
apple fruit 0 72 0 13
mango fruit 16 0 19 0
carrot veg 49 0 12 0
tomato veg 0 9 0 21
the resultant set Iam looking is
A B C D E F
apple fruit 23 72 25 13
mango fruit 16 12 19 45
carrot veg 49 52 12 23
tomato veg 3 9 16 21
Thanks,
data t1;
input A $ B C D E ;
cards;
apple 23 0 25 0
mango 0 12 0 45
carrot 0 52 0 23
tomato 3 0 16 0
;
run;
data t2;
input A $ B C D E ;
cards;
apple 0 72 0 13
mango 16 0 19 0
carrot 49 0 12 0
tomato 0 9 0 21
;
run;
proc sql;
select t1.a,sum(t1.b,t2.b) as b,
sum(t1.c,t2.c) as c,
sum(t1.d,t2.d) as d,
sum(t1.e,t2.e) as e
from t1,t2
where t1.a=t2.a;
quit;
Hi,
With the real data, in SAS DI you can always use SCD Type1 cluster to address this scenario. In SCD Type1: the new incoming data will update the existing data if there are any changes and keeps the original if no changes in transactions.
Thanks,
Vishnu
proc sql;
create table want as
select t1.a, t1.b,coalesce(t1.c,t2.c) as c, coalesce(t1.d,t2.d) as d, ......
from t1
inner join t2
on t1.a = t2.a
and t1.b = t2.b
;
quit;
If tou have values in both tables for the same key, use sum() instead of coalesce();
//Fredrik
data t1;
input A $ B C D E ;
cards;
apple 23 0 25 0
mango 0 12 0 45
carrot 0 52 0 23
tomato 3 0 16 0
;
run;
data t2;
input A $ B C D E ;
cards;
apple 0 72 0 13
mango 16 0 19 0
carrot 49 0 12 0
tomato 0 9 0 21
;
run;
proc sql;
select t1.a,sum(t1.b,t2.b) as b,
sum(t1.c,t2.c) as c,
sum(t1.d,t2.d) as d,
sum(t1.e,t2.e) as e
from t1,t2
where t1.a=t2.a;
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.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.