topic Re: Hash programing - removdup question in SAS Programming

Hash programing - removdup question

Fae — Wed, 16 May 2018 19:40:30 GMT

I am having trouble with the removedup command in Hash. From what I understand, it remove the current record being pointed in the hash table(memory) and set the pointer to null. Am I missing anything?

Basically I have two data set, sales and refunds. I need to merge the refund data back to the original purchase by matching customer_id, product_id in a FIFO basis as long as refund is <= sales which then will proceed to the next purchase. The record will also have all the refund_id added up.

But I couldn't get it working properly, if i use removedup, each purchase will just take 1 refund and move to the next. Could someone help me? Thanks.

***** Code ****

data Sales;
infile datalines dsd;
input Customer_id Product_Id $ sales transaction_id $;
cards;
1,A1,2,1
1,A1,4,2
1,A2,5,3
2,A1,2,4
2,A2,2,5
;
run;

data Refund;
infile datalines dsd;
input Customer_id Product_Id $ refunds refund_id $;
cards;
1,A1,1,r1
1,A1,1,r2
1,A1,1,r3
2,A1,1,r4
;
run;

data want;
if _n_=1 then do;
if 0 then set refund;
declare hash h(dataset:'refund',multidata:'y');
h.definekey('Customer_id','Product_Id');
h.definedata('refunds','refund_id');
h.definedone();
end;
set sales;
by Customer_id Product_Id;
format r_id $200.;
cum_refund = 0;
r_id = "";
call missing(refunds,refund_id);
rc=h.find();
do while(rc=0);
if sum(refunds, cum_refund) <= sales then
do;
cum_refund = sum(refunds,cum_refund);
r_id = catx("-",r_id,refund_id);
*h.removedup();
call missing(refunds,refund_id);
rc=h.find_next();
end;
else
do;
leave;

end;
end;
drop refunds refund_id rc;
run;

****** Data ********

Have:

Sales:

Customer_ID	Product_id	sales	Transaction_ID
1	A1	2	1
1	A1	4	2
1	A2	5	3
2	A1	2	4
2	A2	2	5

Refund:

Customer_ID	Product_id	Refund	refund_id
1	A1	1	r1
1	A1	1	r2
1	A1	1	r3
2	A1	1	r4

Want :

Customer_ID	Product_id	sales	Transaction_ID	r_id	cum_refund
1	A1	2	1	r1-r2	2
1	A1	4	2	r3	1
1	A2	5	3		0
2	A1	2	4	r4	1
2	A2	2	5		0

Re: Hash programing - removdup question

novinosrin — Thu, 17 May 2018 00:21:27 GMT

I'm afraid this is not quite a removedup question but doesn't matter. I honestly think you can try much more simpler straight forward solutions than hash but that's up to you. Please find the corrected below:

data Sales;
infile datalines dsd;
input Customer_id Product_Id $ sales transaction_id $;
cards;
1,A1,2,1
1,A1,4,2
1,A2,5,3
2,A1,2,4
2,A2,2,5
;
run;

data Refund;
infile datalines dsd;
input Customer_id Product_Id $ refunds refund_id $;
cards;
1,A1,1,r1
1,A1,1,r2
1,A1,1,r3
2,A1,1,r4
;
run;

 

data want;
if _n_=1 then do;
if 0 then set refund;
declare hash h(dataset:'refund',multidata:'y');
h.definekey('Customer_id','Product_Id');
h.definedata('refunds','refund_id');
h.definedone();
end;
set sales;
by Customer_id Product_Id;
format r_id $200.;
cum_refund = 0;
r_id = "";
call missing(refunds,refund_id);
rc=h.find();
do while(rc=0);
if sum(refunds, cum_refund) <= sales then
do;
if cum_refund=2 then do; cum_refund=0;r_id = "";end;/*here is the correction*/
cum_refund = sum(refunds,cum_refund);
r_id = catx("-",r_id,refund_id);
*h.removedup();/*not needed, so I let it stay commented*/
call missing(refunds,refund_id);
rc=h.find_next();
end;
else
do;
leave;
end;
end;
drop refunds refund_id rc;
run;

Here is the corrected code:

Re: Hash programing - removdup question

FreelanceReinh — Sun, 17 Jun 2018 12:08:11 GMT

@Fae wrote:

I am having trouble with the removedup command in Hash. From what I understand, it remove the current record being pointed in the hash table(memory) and set the pointer to null. Am I missing anything?

Yes, you are. There are, unfortunately, even more details about the REMOVEDUP method than can be found in the documentation. It took me quite some testing to investigate these details (using SAS 9.4 TS1M2).

For example, you may be wondering what the purpose of the "key: ..." argument of the method is, when actually the pointer specifies the hash entry to be removed, as you mentioned.

Indeed, the pointer must be set, regardless of the argument (as long as it is valid) and regardless of values of key variables. (Otherwise you get the "WARNING: No current list item has been set ...".) The effect of the REMOVEDUP method then depends on the type of entry the pointer points to:

Case 1: It belongs to a group of >=2 entries with the same key.

Effect: This entry is removed, unless an error occurs because the group (!) is locked by an iterator or the key argument is invalid (type mismatch or incorrect number of values.) Valid key arguments or values of key variables are ignored, including the case of non-existing key values. The return code is 0.

Case 2: Its key occurs only once in the hash object (still assuming MULTIDATA: 'YES').

Effect: In this case REMOVEDUP acts in the same way as REMOVE! Which means: Now it is the pointer which is ignored henceforth. If an existing key is specified in the argument or (with empty argument) in the key variables, all (!) hash items with this key are removed (and the return code is set to 0), again unless locked by an iterator. If a non-existing (but valid) key is specified, nothing is removed and only in this case the return code is different from 0 (causing an error message if not written to a variable). An invalid key causes error messages.