topic Re: Hash merge in data step mis-behaviour in SAS Programming

Hash merge in data step mis-behaviour

J_CKY — Fri, 07 Apr 2017 08:45:49 GMT

I have written the following code to perform a hash merge within a data-step.

but the steps after the join is not porforming correctly.

data out_set;
set in_set;

length var1 var2 8;
if _n_ =1 then do;
	declare hash h1(dataset: 'lkup_1',multidata:'y');
	rc= h1.defineKey('key1');
	rc= h1.defineData('var1');
	rc= h1.defineDone();

	declare hash h2(dataset: 'lkup_2' , multidata:'y');
	rc= h2.defineKey('key2');
	rc= h2.defineData('var2');
	rc= h2.defineDone();
end;
	rc= h1.find();
	rc= h2.find();
	output;

	do while (rc=0);
	   rc= h1.find_next();
	   rc= h2.find_next();
	   if rc = 0 then output;
	end;

	miss_flag = missing(var1);
	run;

All of the MISS_FLAG are returning null even for obs where var1 is not missing. Ideally, I would like to perform the follows and replace the MISS_FLAG:

VAR = ifn(missing(VAR1), VAR2, VAR1);
drop VAR1 VAR2 rc;

Somehow the drop statement was working, but the step above was not. So i tried to investigate with the miss_flag variable.

Anyone could point me to the right direction would be great.

Thanks.

Re: Hash merge in data step mis-behaviour

Kurt_Bremser — Fri, 07 Apr 2017 09:19:46 GMT

Does in_set contain key1 and key2?

How many observations does in_set contain, and how many are output to out_set?

Re: Hash merge in data step mis-behaviour

Kurt_Bremser — Fri, 07 Apr 2017 09:21:51 GMT

PS It's always good to post the whole log of the step for reference (use the {i} button to open a window that allows posting with a fixed font and no HTML formatting)

Re: Hash merge in data step mis-behaviour

user24feb — Fri, 07 Apr 2017 11:19:46 GMT

Regarding ".. returning null even for obs where var1 is not missing."

Yes, probably because

rc= h1.find_next();
rc= h2.find_next();

causes the rc of h1 to be overwritten by h2. What exactly is going on, is hard to tell without data.

Re: Hash merge in data step mis-behaviour

Ksharp — Fri, 07 Apr 2017 12:52:33 GMT

You have two hash table, so you need two RC.


data out_set;
set in_set;

length var1 var2 8;
if _n_ =1 then do;
	declare hash h1(dataset: 'lkup_1',multidata:'y');
	 h1.defineKey('key1');
	 h1.defineData('var1');
	 h1.defineDone();

	declare hash h2(dataset: 'lkup_2' , multidata:'y');
	 h2.defineKey('key2');
	 h2.defineData('var2');
	 h2.defineDone();
end;
call missing(var1,var2);
	rc1= h1.find();
	rc2= h2.find();
	

	do while (rc1=0 and rc2=0);
           output;
	   rc1= h1.find_next();
	   rc2= h2.find_next();
	end;

	miss_flag = missing(var1);
	run;