Hello @Pandu2
You have the two following requirements.
These two tables should be joined based on those 3 fields named a,b,c and it should have a condition like
If the values of the 3 fields in x doesn't exist in the columns of y table then for those values the amount should be equal to 0 instead of excluding them those
who doesn't exist.
While there could be many approaches to solve this problem, one solution for this is to use a full outer join. Proc SQL or data step merge cam be used. As you are using three columns to base your join upon, one approach is to create a composite key. I have used a simple approach of concatenating the three column values. More sophisticated approaches can be used. Your second table i.e., y has missing values for amount, it is better to have them as zero. (you may need them in arithmetic operations. Using missing values in an expression generates missing values. This can be avoided by having them as zeros rather than keep them missing). I suggest the following code. Modify to suit your needs.
data x;
input (a b c) (:$4.);
datalines;
1 5 15
2 6 26
3 7 37
4 8 48
;
data y;
infile datalines truncover;
input (a b c) (:$4.) amount :10.;
datalines;
1 5 15 4
2 6 26 5
3 7 36 9
4 8 48
;
data left;
retain key a b c;
set x;
key=catx('-',a,b,c);
run;
data right;
retain key a b c;
set y;
key=catx('-',a,b,c);
run;
proc sort data=left;
by key;
run;
proc sort data=right;
by key;
run;
data want (drop=key);
merge left right;
by key;
amount=coalesce(amount,0);
run;
The output will be like this
... View more
