incorrect time format

[aishajennifer1]

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.

[ballardw]

---

@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 · Accepted solution]

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.

---