Eliminate record without know when to stop the process

Accepted Solution Solved
Reply
Super Contributor
Posts: 371
Accepted Solution

Eliminate record without know when to stop the process

[ Edited ]

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;


Accepted Solutions
Solution
‎02-02-2016 11:28 AM
Super User
Posts: 6,936

Re: Eliminate record without know when to stop the process

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super Contributor
Posts: 371

Re: Eliminate record without know when to stop the process

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;

 

Super User
Super User
Posts: 7,401

Re: Eliminate record without know when to stop the process

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;
Super User
Posts: 9,681

Re: Eliminate record without know when to stop the process

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;
Solution
‎02-02-2016 11:28 AM
Super User
Posts: 6,936

Re: Eliminate record without know when to stop the process

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 371

Re: Eliminate record without know when to stop the process

that is amazing code!

Thank you.

HHC

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 354 views
  • 1 like
  • 4 in conversation