SAS DI Merging

[vickys]

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,

[Vish33]

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

[FredrikE]

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

[Ksharp · Accepted solution]

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;