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

Hi. I currently have a dataset that is not formatted properly.

 

Example: As you can see starttime needs to be formatted to other values. How do I do this without creating additional columns?

 

subjectidstartdatestarttime
11/2/20191947
21/3/20191801
31/4/201910:59:51
41/5/20191445

 

I am ok to just capture HOUR:MINUTE. No need for second so subject3 can have 1059. I tried to concat and merge it in but it is too many steps.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

Insert a colon if the length is 4 (substr() and catx() functions), and then use the time5. informat on the first 5 characters.

 


@aishajennifer1 wrote:

Hi. I currently have a dataset that is not formatted properly.

 

Example: As you can see starttime needs to be formatted to other values. How do I do this without creating additional columns?

 

subjectid startdate starttime
1 1/2/2019 1947
2 1/3/2019 1801
3 1/4/2019 10:59:51
4 1/5/2019 1445

 

I am ok to just capture HOUR:MINUTE. No need for second so subject3 can have 1059. I tried to concat and merge it in but it is too many steps.

 

 


 

View solution in original post

2 REPLIES 2
ballardw
Super User

@aishajennifer1 wrote:

Hi. I currently have a dataset that is not formatted properly.

 

Example: As you can see starttime needs to be formatted to other values. How do I do this without creating additional columns?

 

subjectid startdate starttime
1 1/2/2019 1947
2 1/3/2019 1801
3 1/4/2019 10:59:51
4 1/5/2019 1445

 

I am ok to just capture HOUR:MINUTE. No need for second so subject3 can have 1059. I tried to concat and merge it in but it is too many steps.

 

 


The typical cause I see for this sort of behavior is data manually entered into a spreadsheet like Excel with inconsistent standards so that some of the values are actual times and others end up as character values.

In SAS that usually translates to some values looking like numbers as shown but the values are CHARACTER.  At which point I am not sure what the Excel "time" conversion should be.

 

If you used proc import to read the data from Excel I suggest:

1) go back to your Excel file.

2) make sure the entire column has the cells formatted as time

3) Save the file as a CSV file

4) import that csv file, using the Guessingrows=max ; option.

 

Or share the code you used to read the data.

 

Once a variable in SAS is created you cannot change it's type and I suspect that you have character values which aren't going to let you have actual SAS time values, which are numeric.

Kurt_Bremser
Super User

Insert a colon if the length is 4 (substr() and catx() functions), and then use the time5. informat on the first 5 characters.

 


@aishajennifer1 wrote:

Hi. I currently have a dataset that is not formatted properly.

 

Example: As you can see starttime needs to be formatted to other values. How do I do this without creating additional columns?

 

subjectid startdate starttime
1 1/2/2019 1947
2 1/3/2019 1801
3 1/4/2019 10:59:51
4 1/5/2019 1445

 

I am ok to just capture HOUR:MINUTE. No need for second so subject3 can have 1059. I tried to concat and merge it in but it is too many steps.

 

 


 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 2 replies
  • 364 views
  • 0 likes
  • 3 in conversation