I am having trouble getting a fuzzy hash merge to work on dates. The price table is a list of prices that have changed over time. For example, Item A cost $1 from 1/1/2018 to 6/1/2018, and $2 from 6/2/2018 to 12/31/2018. The receipts table is a list of items that were purchased, and the date they were purchased. The code below is attempting to match the two datasets, but it does not appear to be using the key fields to match - instead, it only matches on the date ranges. What am I doing wrong? Let me know if I need to provide more information. data merge;
if 0 then set prices;
/* On the first iteration of the step, declare and instantiate */
/* the hash object H and hash iterator HITER. Use the DATASET: */
/* argument tag to load WORK.TWO into H. Define the 'lookup key' */
/* for the hash table H using DEFINEKEY. Define the KEY's */
/* associated values with DEFINEDATA. The KEY is not */
/* automatically output to the data set, so add STARTRANGE as */
/* data so it will be included in WORK.OUT. */
if _n_=1 then do;
declare hash h(dataset:'prices');
h.definekey('location','item','start_date');
h.definedata('location','price','start_date','end_date','name');
declare hiter hiter('h');
h.definedone();
end;
set receipts;
/* Move to the first item in the hash table using the method FIRST()*/
rc=hiter.first();
/* For each observation, iterate through the hash table */
/* until the value of date is between the value of start_date */
/* and end_date. */
do while (rc=0);
if start_dt le date le end_dt then leave;
rc=hiter.next();
end;
run;
... View more
