Hash tables

[Ram_s]

Hello, 

I've got two tables like

data pos;
input date :mmddyy. positive;
format date yymmdd10.;
datalines;
1/12/2014 0
1/13/2014 1
1/14/2014 0
1/15/2014 0
1/16/2014 0
1/17/2014 1
;

data dat;
input datee :mmddyy. cnt;
format datee yymmdd10.;
datalines;
1/12/2014 23
1/14/2014 32
1/16/2014 54
1/15/2014 21
1/15/2014 34
1/13/2014 42
;

and i would like to join them with hash to get table like

date               datee                cnt

1/13/2014      1/12/2014         23
1/17/2014      1/14/2014         32
1/17/2014      1/16/2014         54
1/17/2014      1/15/2014         21
1/17/2014      1/15/2014         34
1/13/2014      1/13/2014         42

it means to each date from dat join next date from pos with 1 on column positive.

By now I've got this code

data def;
format date yymmdd10. ;
length positive 8;
dcl hash nwd (dataset: 'pos');
nwd.definekey('date');
nwd.definedata('positive');
nwd.definedone();

do until (eof);
set dat end=eof;

rc=nwd.find(key: datee);
if (positive=0) then do;
date=date+1;
end;
output;
end;
run;

but it adds only one day.

Thanks for help

[Kurt_Bremser · Accepted solution]

Why not use a SQL query?

proc sql;
create table want as
  select
    t2.date,
    t1.datee,
    t1.cnt
  from dat t1 left join pos (where=(positive)) t2
  on t1.datee le t2.date
  group by t1.datee
  having t2.date = min(t2.date)
;
quit;

Result:

1	2014-01-13	2014-01-12	23	
2	2014-01-13	2014-01-13	42	
3	2014-01-17	2014-01-14	32	
4	2014-01-17	2014-01-15	34	
5	2014-01-17	2014-01-15	21	
6	2014-01-17	2014-01-16	54	

[PeterClemmensen]

My 2 cents

data want (drop = rc);
   if _N_ = 1 then do;
      declare hash h (dataset : 'pos(where=(positive=1))', ordered : 'A');
      h.definekey ('date');
      h.definedone ();
      declare hiter hi ('h');
   end;

   set dat;
   date = .;

   do until (date >= datee);
      rc = hi.next();
   end;

   format date yymmdd10.;
run;

Result:

datee       cnt date 
2014-01-12  23  2014-01-13 
2014-01-14  32  2014-01-17 
2014-01-16  54  2014-01-17 
2014-01-15  21  2014-01-17 
2014-01-15  34  2014-01-17 
2014-01-13  42  2014-01-13 

[Kurt_Bremser]

My idea for using a hash:

proc sql noprint;
select max(date) into :maxdate from pos
where positive;
quit;

data want;
set dat;
if _n_ =1
then do;
  declare hash pos (dataset:"pos (where=(positive))");
  pos.definekey("date");
  pos.definedone();
  format date yymmddd10.;
end;
date = datee;
do while (pos.find() ne 0 and date <= &maxdate);
  date + 1;
end;
if date > &maxdate
then do;
  date = .;
  put "no date found:" datee= cnt=;
end;
run;

[Ram_s]

thanks, both of yours solutions really helped

[novinosrin]

Good morning @Ram_s , If I understand you correctly, your approach basically loads the entire table "pos" in Hash memory resident table with values 0's and 1's. Are you trying to do something like Key enumerate and Enumerate all as a combination? Perhaps for some learning purpose or does the business mandate to ride from key enumerate find and pick the immediate available date in succession that occurs after the datee?

The logic seemingly makes me concur with approach of others in only loading positive=1 records in the Hash table, albeit your approach made me wonder whether your appetite for learning stretches to what methinks is likely the case. Otherwise, please ignore by all means.

I just tweaked your code with the above stated assumption:-

data pos;
input date :mmddyy. positive;
format date yymmdd10.;
datalines;
1/12/2014 0
1/13/2014 1
1/14/2014 0
1/15/2014 0
1/16/2014 0
1/17/2014 1
;

data dat;
input datee :mmddyy. cnt;
format datee yymmdd10.;
datalines;
1/12/2014 23
1/14/2014 32
1/16/2014 54
1/15/2014 21
1/15/2014 34
1/13/2014 42
;

data def;
 dcl hash nwd (dataset: 'pos',ordered:'y');
 nwd.definekey('date');
 nwd.definedata('date','positive');
 nwd.definedone();
 dcl hiter nht('nwd');
 do until (eof);
  set dat end=eof;
  do rc=nht.setcur(key:datee) by 0 while(rc=0 and not positive);
   rc=nht.next();
  end;
  output;
 end;
 stop;
 set pos;
 drop rc;
run;

proc print noobs;run;

datee	cnt	positive	date
2014-01-12	23	1	2014-01-13
2014-01-14	32	1	2014-01-17
2014-01-16	54	1	2014-01-17
2014-01-15	21	1	2014-01-17
2014-01-15	34	1	2014-01-17
2014-01-13	42	1	2014-01-13

[Ram_s]

thanks, it works properly