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

I have an excel sheet where the data set has columns with military time but no separators. How to convert the military time to hh: mm. For example, the columns have military time like 1509,7,30,637.

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21
data have;
input COLLECTION_TIME $ RECV_TIME $;
datalines;
1509    613
2033    625
30  615
2130    603
2114    720
1009    639
1457    553
2131    634
627 550
1702    632
;

data want;
set have;
t = input(COLLECTION_TIME, best.);
COLLECTION_TIME_DT = hms(int(t/100), mod(t,100), 0);
t = input(RECV_TIME, best.);
RECV_TIME_DT = hms(int(t/100), mod(t,100), 0);
format COLLECTION_TIME_DT RECV_TIME_DT hhmm5.;
run;

proc print data=want; run;

PGStats_0-1620000187573.png

 

PG

View solution in original post

14 REPLIES 14
PGStats
Opal | Level 21

According to which standard (reference) ?

PG
SriCh1
Calcite | Level 5

Military time is 24 hr time, but it is in 1507 format which is 03:07 PM, I want to convert all the values to HH MM format.

PGStats
Opal | Level 21
time = hms(input(substr(mTime,1,2), best.), input(substr(mTime,3,2), best.), 0); 
PG
SriCh1
Calcite | Level 5

Is the solution for SAS?

SriCh1
Calcite | Level 5

The given solution did not work but I can provide you with an example of the dataset.

COLLECTION_TIMERECV_TIME
1509613
2033625
30615
2130603
2114720
1009639
1457553
2131634
627550
1702632
PaigeMiller
Diamond | Level 26

Show us the code you used and the results. Explain what was wrong with the answers.

--
Paige Miller
SriCh1
Calcite | Level 5
I tried the code but the values were all missing
PaigeMiller
Diamond | Level 26

Repeating: "Show us the code you used"

--
Paige Miller
SriCh1
Calcite | Level 5
time = hms(input(substr(mTime,1,2), best.), input(substr(mTime,3,2), best.), 0);
PaigeMiller
Diamond | Level 26

If you didn't change the variable names from the code by @PGStats to the varaible names in your data set, then I would expect missing values.

 

Show us the whole data step. Also, if there are any WARNINGs in the log, show those to us as well. (or just show us the entire LOG from this data step including CODE, ERRORs, WARNINGs and NOTEs)

--
Paige Miller
PGStats
Opal | Level 21
data have;
input COLLECTION_TIME $ RECV_TIME $;
datalines;
1509    613
2033    625
30  615
2130    603
2114    720
1009    639
1457    553
2131    634
627 550
1702    632
;

data want;
set have;
t = input(COLLECTION_TIME, best.);
COLLECTION_TIME_DT = hms(int(t/100), mod(t,100), 0);
t = input(RECV_TIME, best.);
RECV_TIME_DT = hms(int(t/100), mod(t,100), 0);
format COLLECTION_TIME_DT RECV_TIME_DT hhmm5.;
run;

proc print data=want; run;

PGStats_0-1620000187573.png

 

PG
SriCh1
Calcite | Level 5
So how can we enter all the observations from the dataset under datalines if we have more than 3000 observations?
Tom
Super User Tom
Super User

@SriCh1 wrote:
So how can we enter all the observations from the dataset under datalines if we have more than 3000 observations?

Why would you want to do that if you already have the data in the dataset you imported from Excel?

SriCh1
Calcite | Level 5
Thank you so much the code worked!

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 14 replies
  • 1238 views
  • 0 likes
  • 4 in conversation