I have to confess, this query is one of the most complicated queries I have ever encountered. I have try to apply all of the rules (complicated by themself already) addressed by OP's first post. Here is what I can come up with:
data queued;
input QueueName $24. acct_num tran_dt :mmddyy8. tran_tm : $8. ;
format tran_dt mmddyy10. acct_num 16.;
tran_tm1=input(tran_tm, b8601tm.);
tran_dtm=dhms(tran_dt,0,0, tran_tm1);
format tran_dtm datetime20.;
cards;
C-BXX-N-AE-XXXXXXXXXX 1000000000801180 3/4/2012 170458
N-2XX-N-KE-XXXXXXX 1000000000801180 3/4/2012 170458
N-PXX-N-CP-XXXXXXXXXXXXX 2000000000885230 3/3/2012 213205
C-BXX-D-CP-XXXXXXX 3000000000064278 3/6/2012 202257
C-BXX-N-AE-XXXXXXXXXX 3000000000064278 3/7/2012 202257
C-RXX-N-AE-XXXXXXXXXXXXX 3000000000064278 3/7/2012 202257
C-VXX-D-CP-XXXXXXX-XXXX 3000000000064278 3/7/2012 202257
N-BXX-N-CP-XXXXXXXXXXXXX 3000000000064278 3/7/2012 202257
N-BXX-N-CP-XXXXXXXXXXXX 3000000000064278 3/7/2012 202257
C-BXX-D-CP-XXXXXXX 3000000000064278 3/7/2012 202312
C-BXX-N-AE-XXXXXXXXXX 3000000000064278 3/7/2012 202312
C-RXX-N-AE-XXXXXXXXXXXXX 3000000000064278 3/7/2012 202312
C-VXX-D-CP-XXXXXXX-XXXX 3000000000064278 3/7/2012 202312
N-BXX-N-CP-XXXXXXXXXXXXX 3000000000064278 3/7/2012 202312
N-BXX-N-CP-XXXXXXXXXXXX 3000000000064278 3/8/2012 233701
;
run;
data worked;
input _QueueName $24. _tran_dt :mmddyy8. _tran_tm : $8. acct_num workedkey _acct_num;
format _tran_dt mmddyy8. acct_num 16.;
_tran_tm1=input(_tran_tm, b8601tm.);
_tran_dtm=dhms(_tran_dt,0,0, _tran_tm1);
QueueName=_QueueName;
format _tran_dtm datetime20.;
cards;
Inbound 3/8/2012 172000 3000000000064278 11925138 3000000000064278
C-VXX-D-CP-XXXXXXX-XXXX 3/7/2012 202400 3000000000064278 11918651 3000000000064278
N-PXX-N-CP-XXXXXXXXXXXXX 3/3/2012 213400 2000000000885230 11886991 2000000000885230
N-2XX-N-KE-XXXXXXX 3/4/2012 170800 1000000000801180 11895835 1000000000801180
;
run;
proc sort data=queued;
by acct_num tran_dtm;
run;
proc sort data=worked (rename=workedkey=workedid);
by acct_num descending _tran_dtm;
run;
data _null_ ;
if _n_=1 then do;
set queued (obs=1);
dcl hash q1(dataset:'queued', ordered: 'a');
q1.definekey('acct_num','QueueName');
q1.definedata('acct_num','QueueName','tran_dt','tran_tm','tran_dtm');
q1.definedone();
dcl hash q2(dataset:'queued', multidata:'yes');
q2.definekey('acct_num');
q2.definedata('acct_num','QueueName','tran_dt','tran_tm','tran_dtm');
q2.definedone();
dcl hash q3(multidata: 'yes', ordered: 'a');
q3.definekey('acct_num','tran_dtm','QueueName');
q3.definedata('acct_num','tran_dt','QueueName','tran_tm','WorkedInd', 'WorkedId');
q3.definedone();
end;
do until (last.acct_num);
set worked end=done;
length workedind $7.;
by acct_num;
retain Inb_dtm inb_workedid pre_workedid;
if _QueueName='Inbound' then
do;
Inb_dtm=_tran_dtm;
Inb_workedid=Workedid;
end;
_rc=q1.find();
if _rc ne 0 then return;
else
do;
_tran_dt=tran_dt;
_tran_tm=tran_tm;
_tran_dtm=tran_dtm;
pre_workedid=workedid;
end;
do _rc=q2.find() by 0 while (_rc=0);
if QueueName eq _QueueName and tran_dtm <=_tran_dtm then
do;
WorkedInd='Worked';
workedid=pre_workedid;
q3.add();
end;
else if QueueName ne _QueueName and tran_dtm <=_tran_dtm then
do;
WorkedInd='Removed';
workedid=pre_workedid;
q3.add();
end;
else if Inb_dtm >= tran_dtm > _tran_dtm then
do;
WorkedInd='Inbound';
WorkedId= inb_workedid;
q3.add();
end;
else if tran_dtm > inb_dtm then
do;
call missing(WorkedInd, workedid);
q3.add();
end;
_rc=q2.find_next();
end;
end;
call missing (Inb_dtm, inb_workedid, pre_workedid);
if (done) then q3.output(dataset: 'want');
run;
proc print;run;
Kindly Regards,
Haikuo
Update: Oops, has to be SAS 9.2
Click image to register for webinar
