topic Re: SAS DI Merging in SAS Studio

SAS DI Merging

vickys — Thu, 03 May 2018 09:07:20 GMT

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,

Re: SAS DI Merging

Vish33 — Thu, 03 May 2018 10:40:03 GMT

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

Re: SAS DI Merging

FredrikE — Thu, 03 May 2018 10:43:33 GMT

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

Re: SAS DI Merging

Ksharp — Thu, 03 May 2018 12:37:35 GMT

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;