topic Re: Eliminate record without know when to stop the process in SAS Programming

Eliminate record without know when to stop the process

hhchenfx — Tue, 02 Feb 2016 03:06:58 GMT

Hi Everyone,

My problem is that I have a list of customer with the time/id_order when they come to me. Each of them has the time they plan to see a doctor and when they plan to leave.

The thing is that this doctor can only meet one at a time. So basically, the enter value of a certain record must be larger than the value of exit for previous record.

If I have to do it manually, I will keep running the process of:

lag_exit=lag(exit);
if enter<lag_exit then delete;run;

untill the size of the data remain the same, no more delete.

Can you help me to make it automatically run by SAS?

Thank you so much.

data have; input id_order enter exit;
cards;
1 1 5
2 7 20
3 11 15
4 18 22
5 22 43
6 25 100
run;

proc sort; by id_order ; run;

data have; set have;
lag_exit=lag(exit);
if enter<lag_exit then delete;run;

data want; set have;
lag_exit=lag(exit);
if enter<lag_exit then delete;run;

Re: Eliminate record without know when to stop the process

hhchenfx — Tue, 02 Feb 2016 03:33:53 GMT

Oh this code works, but I feel that it is quite weird the code

proc sort data=have ; by decending time;run;

data want;
set have nobs=nobs;
i+1;
del=0;
do j=i+1 to i+20 until (found=1);
set have (keep=enter exit time rename=(enter=ent exit=ext time=t)) point=j;
if enter<ext then do;
del=1;
found=1;
end;
end;
run;

data want; set want;
if time=1 then del=0;
if del=1 then delete;run;

proc sort; by time;run;

Re: Eliminate record without know when to stop the process

Ksharp — Tue, 02 Feb 2016 06:13:33 GMT

I love this question. But you didn't post the output you want yet .

data have; input id_order enter exit; 
cards;
1 1 5
2 7 20
3 11 15
4 18 22
5 22 43
6 25 100
;
run;
data _null_;
if 0 then set have;
declare hash h(dataset:'have',ordered:'a');
declare hiter hi('h');
h.definekey('id_order');
h.definedata(all:'y');
h.definedone();

do while(hi.next()=0);
 n+1;
 rc=h.remove(key:id);
 if enter lt lag_exit and n ne 1 then id=id_order;
  else lag_exit=exit;
end;
 rc=h.remove(key:id);
h.output(dataset:'want');
stop;
run;

Re: Eliminate record without know when to stop the process

Kurt_Bremser — Tue, 02 Feb 2016 09:25:16 GMT

What about:

proc sort data=have;
by enter;
run;

data want;
set have;
retain lag_exit .;
if lag_exit > enter then delete;
else lag_exit = exit;
run;

Re: Eliminate record without know when to stop the process

RW9 — Tue, 02 Feb 2016 10:02:42 GMT

Just a couple of tips as I couldnt read that code. Use the code window - {i} in the post icons - this will preserve the indetation. Also format the code for readability and avoid typo's:

proc sort data=have; 
  by descending time;  /* Note the typo correction */
run;
 
data want;
  set have nobs=nobs;
  i+1;
  del=0;
  do j=i+1 to i+20 until (found=1);
    set have (keep=enter exit time rename=(enter=ent exit=ext time=t)) point=j;
    if enter<ext then do;
      del=1;
      found=1;
    end;
  end;
run;
 
data want; 
  set want;
  if time=1 then del=0;
  if del=1 then delete;
run;
 
proc sort; 
  by time;
run;

As for your actualy code, you may be simpler off doing:

proc sql;
  delete from HAVE
  where exit < (select lag_exit from HAVE);
quit;

Re: Eliminate record without know when to stop the process

hhchenfx — Tue, 02 Feb 2016 16:29:49 GMT

that is amazing code!

Thank you.

HHC