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
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
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
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
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;
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 |
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.