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;
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;
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;
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;
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;
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;
that is amazing code!
Thank you.
HHC
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.