Using a Hash Iterator to Modify an Existing Table

theponcer — Wed, 26 Jun 2019 21:23:01 GMT

I have a data table that needs to be adjusted. I have a separate table that includes the type of adjustment. Both tables contain ID and month fields. I believe I should be able to use a hash iterator to find the ID/month combo that needs to be adjusted, adjust any necessary fields, and then move to the next month within that ID that needs to be adjusted (if necessary). However, I am not sure how to code this because I am not familiar with using hash iterators. I realize I could do this code if all of the ID/Month/Adjustment combinations were in the hash table, but I am trying to use the hash iterator. I am thinking it would look something like this:

Rules
1) If you Quit Your Job, you stop receiving your $1 paycheck in that month and all subsequent months
2) If you get a raise, you get a $1 boost to your paycheck for that month and all subsequent months
3) If you win the lottery, you get $1,000,000 in addition to your paycheck for that month only

Have - Data Table
ID	Name	Month	Money
1	Billy	Jan	$1
1	Billy	Feb	$1
1	Billy	Mar	$1
2	Willy	Jan	$1
2	Willy	Feb	$1
2	Willy	Mar	$1
3	Jilly	Jan	$1
3	Jilly	Feb	$1
3	Jilly	Mar	$1

Have - Hash Table
ID	Month	Adjustment
1	Feb	Quit Job
2	Jan	Got a Raise
3	Mar	Won the Lottery

Want
ID	Name	Month	Money
1	Billy	Jan	$1
1	Billy	Feb	$0
1	Billy	Mar	$0
2	Willy	Jan	$2
2	Willy	Feb	$2
2	Willy	Mar	$2
3	Jilly	Jan	$1
3	Jilly	Feb	$1
3	Jilly	Mar	$1,000,001

data start_data;
   input ID $1 Name $3-8 Month $9-11 Money 12-20;
   datalines;
1 Billy Jan 1
1 Billy Feb 1
1 Billy Mar 1
2 Willy Jan 1
2 Willy Feb 1
2 Willy Mar 1
3 Jilly Jan 1
3 Jilly Feb 1
3 Jilly Mar 1
;
run;

data adj_data;
   input ID $1 Month $3-5 Adjustment $6-35;
   datalines;
1 Feb Quit Job
2 Jan Got a Raise
3 Mar Won the Lottery
;
run;

data end_data;
   input ID $1 Name $3-8 Month $9-11 Money 12-20;
   datalines;
1 Billy Jan 1
1 Billy Feb 0
1 Billy Mar 0
2 Willy Jan 2
2 Willy Feb 2
2 Willy Mar 2
3 Jilly Jan 1
3 Jilly Feb 1
3 Jilly Mar 1000001
;
run;

*Rules
1) If you quit your job, you don't get a paycheck for subsequent months
2) If you get a raise, your paycheck increases by $1 for all subsequent months
3) If you win the lottery, you get a one time bonus of $1,000,000;

data end_data_test;
set start_data;
if 0 then set adj_data;
if _n_=1 then do;
	dcl hash adj(dataset:"adj_data");
		adj.definekey("ID","Month");
		adj.definedata("ID","Month","Adjustment");
		adj.definedone();
	declare hiter h("adj_data");
end;
h.first(); *find the first match on the adjustment record;
	if Adjustment = "Quit Job" then do
	Money = 0; *If it is the "Quit Job" adjustment, set the first observation's Money amount to 0;
		do while (adj.find()=0);
			h.next(); *Set all subsequent month's Money amounts to 0 for this ID;
			Money=0;
		end;
	end;
	else if Adjustment = "Got a Raise" then  do
	Money = Money+1;
		do while (adj.find()=0);
			h.next();
			Money=Money+1;
		end;
	end;
	else if Adjustment = "Won the Lottery" then 
	Money = Money+1000000;
	*Here is code for how I would do this if the hash table included adjustments for each
	ID/month combo;
/*rc_adj=adj.find();*/
/*if rc_adj=0 then do;*/
/*	if Adjustment = "Quit Job" then*/
/*	Money = 0;*/
/*	else if Adjustment = "Got a Raise" then */
/*	Money = Money+1;*/
/*	else if Adjustment = "Won the Lottery" then */
/*	Money = Money+1000000;*/
/*	end;*/
/*else Adjustment="";*/

run;

Re: Using a Hash Iterator to Modify an Existing Table

ScottBass — Thu, 27 Jun 2019 05:25:09 GMT

For the example as you have posted, you don't need a hash iterator. There is no need to iterate over the hash.

Try this:

options nocenter;

data start_data;
   input ID $1 Name $3-8 Month $9-11 Money 12-20;
   datalines;
1 Billy Jan 1
1 Billy Feb 1
1 Billy Mar 1
2 Willy Jan 1
2 Willy Feb 1
2 Willy Mar 1
3 Jilly Jan 1
3 Jilly Feb 1
3 Jilly Mar 1
;
run;

data adj_data;
   input ID $1 Month $3-5 Adjustment $6-35;
   datalines;
1 Feb Quit Job
2 Jan Got a Raise
3 Mar Won the Lottery
;
run;

data end_data;
   input ID $1 Name $3-8 Month $9-11 Money 12-20;
   datalines;
1 Billy Jan 1
1 Billy Feb 0
1 Billy Mar 0
2 Willy Jan 2
2 Willy Feb 2
2 Willy Mar 2
3 Jilly Jan 1
3 Jilly Feb 1
3 Jilly Mar 1000001
;
run;

data test;
   * set PDV order and attributes ;
   if 0 then set start_data adj_data;

   * note that data set columns have an implied retain, which is what we want here ;
   * retain adjustment;  * you can add this if you wish but it is redundant (comment out and see for yourself) ;
   if (_n_ = 1) then do;
      declare hash _h1 ( dataset: "adj_data ( keep=id month adjustment )" , hashexp: 16 , ordered: "N" );
      _h1.defineKey("id","month");
      _h1.defineData("adjustment");
      _h1.defineDone();
   end;
   set start_data;
   by id;
   if first.id then call missing(adjustment);  * required due to the implied (or explicit) retain ;
   _rc_h1 = _h1.find();
   select(adjustment);
      when('Quit Job')        money=0;
      when('Got a Raise')     money=money+1;
      when('Won the Lottery') money=money+1000000;
      otherwise;
   end;
run;

proc compare base=end_data comp=test;
run;

topic Using a Hash Iterator to Modify an Existing Table in SAS Programming

Using a Hash Iterator to Modify an Existing Table

Re: Using a Hash Iterator to Modify an Existing Table