Hash programing - removdup question

Reply
Occasional Contributor Fae
Occasional Contributor
Posts: 17

Hash programing - removdup question

[ Edited ]

 

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_IDProduct_idsalesTransaction_ID
1A121
1A142
1A253
2A124
2A225

 

Refund:

Customer_IDProduct_idRefundrefund_id
1A11r1
1A11r2
1A11r3
2A11r4

 

Want :

 

Customer_IDProduct_idsalesTransaction_IDr_idcum_refund
1A121r1-r22
1A142r31
1A253 0
2A124r41
2A225 0

 

PROC Star
Posts: 1,833

Re: Hash programing - removdup question

[ Edited ]

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:

Trusted Advisor
Posts: 1,256

Re: Hash programing - removdup question


@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.

 

 

 

 

Ask a Question
Discussion stats
  • 2 replies
  • 148 views
  • 0 likes
  • 3 in conversation