topic Re: Left join using HASH - null values for missing values in SAS Programming

Left join using HASH - null values for missing values

Myurathan — Sun, 02 Aug 2020 20:49:53 GMT

Hi Experts,

I want to left join two tables (table A and table B).

Table A

Date_ID	ID	Flag
20190202	14715	Y
20190203	14715	Y
20190204	14715	Y
20190202	888	Y
20190203	888	Y

Table B

Date_ID	ID	CID
20190202	14715	222
20190203	14715	222
20190204	14715	222
20190202	14715	333
20190203	14715	333
20190204	14715	333
20190202	1295128	444
20190203	1295128	444
20190204	1295128	444

Code I'm using

data w(drop=rc);
   if _N_ = 1 then do;
      dcl hash h(dataset : "a",hashexp : 20);
      h.definekey("ID","DATE_ID");
      h.definedata("ID","Flag");
      h.definedone();
   end;

   set b;
   if 0 then set a;

   rc = h.find(key : ID, key : DATE_ID);
run;

Table W (What I am getting)

Date_ID	ID	CID	Flag
20190202	14715	222	Y
20190203	14715	222	Y
20190204	14715	222	Y
20190202	14715	333	Y
20190203	14715	333	Y
20190204	14715	333	Y
20190202	1295128	444	Y
20190203	1295128	444	Y
20190204	1295128	444	Y

Table W (what I am expecting)

Date_ID	ID	CID	Flag
20190202	14715	222	Y
20190203	14715	222	Y
20190204	14715	222	Y
20190202	14715	333	Y
20190203	14715	333	Y
20190204	14715	333	Y
20190202	1295128	444
20190203	1295128	444
20190204	1295128	444

Please help me correct error in my code.

Thanks in advance.

Re: Left join using HASH - null values for missing values

PeterClemmensen — Sun, 02 Aug 2020 20:56:02 GMT

Try this

data w(drop=rc);
   if _N_ = 1 then do;
      dcl hash h(dataset : "a",hashexp : 20);
      h.definekey("ID","DATE_ID");
      h.definedata("ID","Flag");
      h.definedone();
   end;

   set b;
   if 0 then set a;
   call missing(flag);

   rc = h.find(key : ID, key : DATE_ID);
run;

Re: Left join using HASH - null values for missing values

Kurt_Bremser — Sun, 02 Aug 2020 20:56:16 GMT

Instead of

if 0 then set a;

set the required attributes (length of flag) in the _n_ = 1 block.

Re: Left join using HASH - null values for missing values

Myurathan — Sun, 02 Aug 2020 21:04:26 GMT

Thank you for your help. It works like a charm.

Re: Left join using HASH - null values for missing values

PeterClemmensen — Sun, 02 Aug 2020 21:08:46 GMT

Anytime