topic Re: Hash tables in New SAS User

Hash tables

Ram_s — Fri, 24 Apr 2020 09:11:47 GMT

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

Re: Hash tables

Kurt_Bremser — Fri, 24 Apr 2020 09:25:08 GMT

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

Re: Hash tables

PeterClemmensen — Fri, 24 Apr 2020 09:25:52 GMT

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

Re: Hash tables

Kurt_Bremser — Fri, 24 Apr 2020 09:34:42 GMT

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;

Re: Hash tables

novinosrin — Fri, 24 Apr 2020 12:34:42 GMT

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

Re: Hash tables

Ram_s — Mon, 04 May 2020 07:54:58 GMT

thanks, it works properly

Re: Hash tables

Ram_s — Mon, 04 May 2020 07:56:10 GMT

thanks, both of yours solutions really helped