How do I get the min time an max time by number? Data looks like this
id. Number. Start time. End time
1a. 23. 01/01/16. 11:01:00. 01/01/16 11:05:11
1a. 24. 01/01/16 11:02:00. 01/01/16 11:06:16
1a. 25. 01/01/16. 12:00:00. 01/0116 12:07:00
I would want want it to look like this outpout
id. Number. Min Start time. Max End time
1a. 23. 01/01/16. 11:01:00. 01/01/16 11:06:11
thanks
Here could give you a start :
option datestyle=mdy;
data have;
input id $ Number (Starttime Endtime) (: anydtdtm.);
format Starttime Endtime datetime.;
cards;
1a. 23 01/01/16:11:01:00 01/01/16:11:05:11
1a. 24 01/01/16:11:02:00 01/01/16:11:06:16
1a. 25 01/01/16:12:00:00 01/01/16:12:07:00
;
run;
data temp;
set have;
if lag(Endtime) lt Starttime or id ne lag(id) then g+1;
run;
data want;
set temp;
by g;
retain start num;
if first.g then do;start=Starttime;num=number;end;
if last.g then do;end=Endtime;output;end;
format start end datetime.;
drop Starttime Endtime number;
run;
Proc Means, Summary, Univariate, SQL, a Data Step
There are a lot of ways to generate descriptive statistics.
What do you want to do here? 1a. 23. seems to be a unique combination, so why assign 01/01/16 11:06:11 to this key as the End time?
what would be your decision rule here?
Ah! That makes sense 🙂
Try the following:
proc sql; create table work.test as select id, number, min(start_time) format=date9. (or whatever format you like), max(end_time) format=date9.(or whatever format you like) from <yrtablename> group by id, number; quit;
Hope this helps
Shouldn't you change the 24 into 23? That's what I understood from your previous reaction...
You suggested that your data should look like:
id. Number. Start time. End time
1a. 23. 01/01/16. 11:01:00. 01/01/16 11:05:11
1a. 23. 01/01/16 11:02:00. 01/01/16 11:06:16
1a. 25. 01/01/16. 12:00:00. 01/0116 12:07:00
Or did I get you wrong here?
@BETO Post your code, the code posted references end_time in the max function.
Hopefully the code now works for you!?
You have a datetime variable, not date, so try datetime21. or dtdate9. formats.
I guess that in total this should do your trick:
option datestyle=mdy; data have; input id $ Number (Starttime Endtime) (: anydtdtm.); format Starttime Endtime datetime.; cards; 1a. 23 01/01/16:11:01:00 01/01/16:11:05:11 1a. 23 01/01/16:11:02:00 01/01/16:11:06:16 1a. 25 01/01/16:12:00:00 01/01/16:12:07:00 ; run; proc sql; create table want as select id, number, /*starttime, endtime,*/ min(starttime) format=datetime., max(endtime) format=datetime. from have group by id, number; quit;
Note that you should omit starttime and endtime as direct outputs of your sql-statement. The group by won't work if you do, because your rows are not uniquely identified by only id and number.
Hopefully this is what you want.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: