BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
saslove
Quartz | Level 8

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Vince28_Statcan
Quartz | Level 8

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.

View solution in original post

3 REPLIES 3
Tom
Super User Tom
Super User

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;

Vince28_Statcan
Quartz | Level 8

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.

saslove
Quartz | Level 8

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 Smiley Happy

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 3168 views
  • 3 likes
  • 3 in conversation