- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Proc Means, Summary, Univariate, SQL, a Data Step
There are a lot of ways to generate descriptive statistics.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Is there a way using proc sql or a data step ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Here is data
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'm getting
id. Number. Start time. End time
1a. 23. 01/01/16. 11:01:00. 01/01/16 11:05:11
when it should be this
id. Number. Start time. End time
1a. 23. 01/01/16. 11:01:00. 01/01/16 11:06:00
the min part works but max it give me the end time to the min what I'm looking for the max time to the end time almost there thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@BETO Post your code, the code posted references end_time in the max function.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hopefully the code now works for you!?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Proc sql ;
Create table report as
(Select
ID,
NUMBER,
START_TIME,
END_TIME,
MIN (START_TIME) FORMAT=DATE9.,
MAX (END_TIME)FORMAT=DATE9.
FROM REPORT
GROUP by Id, numbe
); run;
The date9. Is coming up 1.76501E9
Yes it's number 23
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
You have a datetime variable, not date, so try datetime21. or dtdate9. formats.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content