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

I have a series of date-time variables in the format

mm/dd/yyyy hhmm

 

I need to split these into date variables and time variables. The dates work okay, and sometimes the times work, but sometimes the time is slightly wrong.

 

Datetime examples:

10/26/2016 1400

10/26/2016 1826

1/29/2017 1004

 

If I use the code:

date1 = scan(datetime, 1, ' ');
date = input(date1, mmddyy10.);

time1 = substr(datetime, max(1, length(datetime) - 4));
time = input(substr(time1, 1, length(time1) - 2) || ':' || substr(time1, length(time1) - 1), time5.);

 

Then I get the output

Date (#):            Time (#):

10/26/2016        14:00  -> right

10/26/2016        18:02  -> wrong

1/29/2017          10:00  -> wrong

 

 

However, if I don't try to make time a number and use the code:

 

date1 = scan(datetime, 1, ' ');
date = input(date1, mmddyy10.);

time1 = substr(datetime, max(1, length(datetime) - 4));
time = substr(time1, 1, length(time1) - 2) || ':' || substr(time1, length(time1) - 1);

 

Then I get the output

Date (#):            Time (char):

10/26/2016        14:00  -> right

10/26/2016        18:26  -> right

1/29/2017          10:04  -> right

 

I need time to be numeric so that I can merge with other datasets that have time as a number. Any help would be appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

Use scan() for time1 also:

data test (keep=datetime date time);
input datetime $26.;
format date mmddyy10. time time5.;
date1 = scan(datetime, 1, ' ');
date = input(date1, mmddyy10.);
time1 = scan(datetime,2,' ');
time = input(substr(time1,1,2)!!':'!!substr(time1,3),time5.);
cards;
10/26/2016 1400
10/26/2016 1826
1/29/2017 1004
;
run;

View solution in original post

3 REPLIES 3
Kurt_Bremser
Super User

Use scan() for time1 also:

data test (keep=datetime date time);
input datetime $26.;
format date mmddyy10. time time5.;
date1 = scan(datetime, 1, ' ');
date = input(date1, mmddyy10.);
time1 = scan(datetime,2,' ');
time = input(substr(time1,1,2)!!':'!!substr(time1,3),time5.);
cards;
10/26/2016 1400
10/26/2016 1826
1/29/2017 1004
;
run;
ColeG
Obsidian | Level 7

Ugh I knew it would be something simple. Thank you! Can you explain why scan works here but substring doesn't?

Kurt_Bremser
Super User

@ColeG wrote:

Ugh I knew it would be something simple. Thank you! Can you explain why scan works here but substring doesn't?


Your error was here:

time1 = substr(datetime, max(1, length(datetime) - 4));

Use

time1 = substr(datetime, max(1, length(datetime) - 3));

instead.

Your time1 always started with a blank, and had a length of 5.

That leading blank (which propagates through the rest of processing) seems to confuse the time5. informat, so that it starts reading the minutes from the 4th position (of a 6-byte string!), where the ':' is.

Confirm this by adding an additional line of input data:

10/26/2016 1430

You'll get a time of 14:03 with your original code.

 

That's why I prefer to use scan() instead of substr()s. Has less potential for such errors.

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
  • 512 views
  • 0 likes
  • 2 in conversation