Hello SAS Community
I have a data that looks like this
data dateconv;
input fileid starttime endtime
Datalines;
7 10/1/2013 10:18:45 PM 10/1/2013 10:36:43 PM
8 10/1/2013 11:24:34 PM 10/1/2013 11:46:44 PM
;
run;
I would like to extract the date part for each file ID and report the minimum of start time and maximum of end time.
Output should look like this
Fileid Earliest_start_time Latest_End_time
I am trying to use datepart, but it gives me this error
Invalid numeric data, TripEnd='9/30/2013 7:54:50 PM' , at line 299 column 18.
Please help!
Looks like your datetime are stored as character.
Can you verify that the datatype is numeric and the informat or format are in some datetimen.w format using dataset properties?
If it is stored as character, you would need to do something like
datepart(input(tripend, anydtdtm.)) /* or a more appropriate datetime format. you may need to use options datestyle=MDY although I think the only supported AMPM informat is MDY */
also, I am unsure what you are trying to achieve exactly but since you are talking about start time and not start date, I suspect you are looking for timepart function?
Anyway, once you figure out your data type issue, the min/max could easily be achieved with proc sql min and max aggregate functions with group by fileid or with the use of data step by processing with first.fileid and last.fileid, 2 new variables that you retain throughout and only replace conditionally.
You need to convert your character strings into datetime values before you can apply the datepart() function.
But it is not clear from you question why you want to apply the datepart() function.
data dateconv;
infile cards dlm='|' truncover ;
informat starttime endtime anydtdtm22. ;
format starttime endtime datetime19. ;
input fileid starttime endtime ;
cards;
7|10/1/2013 10:18:45 PM|10/1/2013 10:36:43 PM
8|10/2/2013 11:24:34 PM|10/2/2013 11:46:44 PM
run;
proc means noprint data=dateconv min max ;
var starttime endtime ;
output out=summary min(starttime)= max(endtime)= ;
run;
proc print;
format starttime endtime dtdate9.;
run;
Looks like your datetime are stored as character.
Can you verify that the datatype is numeric and the informat or format are in some datetimen.w format using dataset properties?
If it is stored as character, you would need to do something like
datepart(input(tripend, anydtdtm.)) /* or a more appropriate datetime format. you may need to use options datestyle=MDY although I think the only supported AMPM informat is MDY */
also, I am unsure what you are trying to achieve exactly but since you are talking about start time and not start date, I suspect you are looking for timepart function?
Anyway, once you figure out your data type issue, the min/max could easily be achieved with proc sql min and max aggregate functions with group by fileid or with the use of data step by processing with first.fileid and last.fileid, 2 new variables that you retain throughout and only replace conditionally.
It was a character value and I used the datepart(input(tripend, anydtdtm.)) It works now. Thank you for your help! Really appreciate your time
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.