More about Hash Object Merge !

<<< Click here to go back


Now we would be using only two datasets ( instead of 3) for understanding various versions of merging with hash object :


Suppose, we have Sales data available only for Feb - Jun, But Promotion data is available for Jan - May months :

Data Sales;
Input Year Month Sales;
cards;
2010 2 1472
2010 3 1575
2010 4 1717
2010 5 1287
2010 6 1621
2015 2 1973
2015 3 1801
2015 4 1400
2015 5 1411
2015 6 1461
;
Run;

Data Promotion;
Input Year Month Scheme & $20.;
cards;
2010 1 Discount
2010 2 Freebies
2010 3 Discount
2010 4 Freebies
2010 5 Lucky Draw
2015 1 Lucky Draw
2015 2 Freebies
2015 3 Discount
2015 4 Lucky Draw
2015 5 Freebies
;
Run;

How to Do Inner Join first

Well, the code that we have used in the previous example, actually performs the Inner Join ! Below is the modified version of the same code for only two datasets join:

Data Result_data_Inner ;

/*With the below statement, SAS holds the descriptive portion of the data*/
if 0 then set Sales  Promotion;

/*The below statement prohibits the re-load of the hash object in the memory*/
if _N_ = 1 then do;

/*Now we define the hash object : Promo */
Declare hash Promo (dataset: 'Promotion');
Promo.definekey('Year','Month');
Promo.defineData('Year','Month','Scheme');
Promo.definedone();

end;

/*Now we call the base dataset*/
set Sales;
/*Below we define the matching criterion*/
if  Promo.find() = 0 then output;

Run;

Result_data_Inner

How to Do a Left Join ( Base data on left)

Data Result_data_left ;

/*With the below statement, SAS holds the descriptive portion of the data*/
if 0 then set Sales  Promotion;

/*The below statement prohibits the re-load of the hash object in the memory*/
if _N_ = 1 then do;

/*Now we define the hash object : Promo */
Declare hash Promo (dataset: 'Promotion');
Promo.definekey('Year','Month');
Promo.defineData('Year','Month','Scheme');
Promo.definedone();

end;

/*Now we call the base dataset*/

set Sales;
if Promo.find() = 0 then do ;
Scheme = Scheme;
end;
Else Scheme = "";
Run;

/*Use either of above Pink highlighted or below Green highlighted portion, would result the same !                                 */

Do until (eof);

set Sales end = eof;

Result_Data_Left

if Promo.find() = 0 then output ;

Else do ;
Call missing (scheme);
Output;
End;
End;
Stop;

Run;

I am yet struggling for learning, how to do a full join using the same, till then ...

Enjoy reading our other articles and stay tuned with us.

Kindly do provide your feedback in the 'Comments' Section and share as much as possible.