topic Re: Time: Constant format to hhmmss format in SAS Programming

Time: Constant format to hhmmss format

aska_ujita — Wed, 11 Mar 2020 13:28:48 GMT

Hello!!

I am having trouble with my time format.

First, I imported my Excel sheet and with PROC CONTENTS I saw that a lot of the variables were considered as character. It remains that way even when I formatted in the Excel sheet itself. So I did the conversion using INPUT statement as described below.

But then I got problems with my time data that are CHUTE_T and LATENCY. They have the format of hh:mm:ss in my Excel sheet.

First procedure, I used this: CHUTE_T1=INPUT (CHUTE_T, tod9.) and LATENCY1=INPUT (LATENCY, tod9.).

But got an error: ERROR 48-59: The informat TOD was not found or could not be loaded.

Then I changed the procedure to: CHUTE_T1=INPUT (CHUTE_T, time.) and 654 LATENCY1=INPUT (LATENCY, time.)

Then runs, but didn't give me the hh:mm:ss format... Gave me like this: CHUTE_T 21180470.53 and LATENCY 23557989.47.

Here is the first procedure:

511 PROC IMPORT

512 OUT= WORK.A

513 DATAFILE= "C:\Users\usuario\Desktop\Data1.xlsx"

514 DBMS=xlsx REPLACE;

515 GETNAMES=YES;

516 RUN;

NOTE: The import data set has 429 observations and 32 variables.

NOTE: WORK.A data set was successfully created.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.17 seconds

cpu time 0.14 seconds

517

518 PROC SORT DATA=A;

519 BY GROUP DATE;

520 RUN;

NOTE: There were 429 observations read from the data set WORK.A.

NOTE: The data set WORK.A has 429 observations and 32 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

521

522 PROC CONTENTS;

523 RUN;

NOTE: PROCEDURE CONTENTS used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

524

525 DATA B; SET A;

526 VAGINA1=INPUT(**bleep**, BEST.);

527 RF1=INPUT(RF, BEST.);

528 L_EAR1=INPUT(L_EAR, BEST.);

529 R_EAR1=INPUT(R_EAR, BEST.);

530 NOSE1=INPUT(NOSE, BEST.);

531 CHUTE_T1=INPUT (CHUTE_T, tod9.);

-----

532 LATENCY1=INPUT (LATENCY, tod9.);

-----

ERROR 48-59: The informat TOD was not found or could not be loaded.

533 VOC1=INPUT(VOC, BEST.);

534 RUM1=INPUT(RUM, BEST.);

535 SNO1=INPUT(SNO, BEST.);

536 DIS1=INPUT(DIS, BEST.);

537 MOV1=INPUT(MOV, BEST.);

538 ESC1=INPUT(ESC, BEST.);

539 KICK1=INPUT(KICK, BEST.);

540 LOC1=INPUT(LOC, BEST.);

541 BL1=INPUT(BL, BEST.);

542 VEL_EXT1=INPUT(VEL_EXT, BEST.);

543 CORT1=INPUT(CORT, BEST.);

544 PEN1=INPUT(PEN, BEST.);

545 FS1=INPUT(FS, BEST.);

546 RUN;

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.B may be incomplete. When this step was stopped there were 0 observations

and 52 variables.

WARNING: Data set WORK.B was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

real time 0.04 seconds

cpu time 0.03 seconds

547

548 PROC CONTENTS;

549 RUN;

NOTE: PROCEDURE CONTENTS used (Total process time):

real time 0.03 seconds

cpu time 0.03 seconds

550

551 ods excel file='C:\Users\usuario\Desktop\Sheet1.xls';

552 ods excel options(sheet_interval="none");

553

554 PROC SORT DATA=B;

555 BY GROUP DATE;

556 RUN;

NOTE: Input data set is already sorted, no sorting done.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

557

558 PROC FREQ;

559 TABLE BALK_ENT VEL_ENT VOC1 RUM1 SNO1 DIS1 MOV1 ESC1 KICK1 LOC1 BL1 BALK_EXT VEL_EXT1 PEN1;

560 BY GROUP DATE;

561 RUN;

NOTE: There were 429 observations read from the data set WORK.B.

NOTE: PROCEDURE FREQ used (Total process time):

real time 2.17 seconds

cpu time 2.10 seconds

562

563 PROC MEANS n mean stderr;

564 VAR VAGINA1 RF1 L_EAR1 R_EAR1 NOSE1 CHUTE_T1 LATENCY1 BALK_ENT VEL_ENT VOC1 RUM1 SNO1 DIS1 MOV1

564! ESC1 KICK1 LOC1 BL1 BALK_EXT VEL_EXT1 CORT1 PEN1 FS1;

565 BY GROUP DATE;

566 RUN;

NOTE: There were 429 observations read from the data set WORK.B.

NOTE: PROCEDURE MEANS used (Total process time):

real time 0.53 seconds

cpu time 0.53 seconds

567

568 ods excel close;

NOTE: Writing EXCEL file: C:\Users\usuario\Desktop\Sheet1.xls

569

570

571 ods html close; /* close previous */

572 ods html; /* open new */

NOTE: Writing HTML Body file: sashtml9.htm

---------

Here is the second procedure:

633 PROC IMPORT

634 OUT= WORK.A

635 DATAFILE= "C:\Users\usuario\Desktop\Data1.xlsx"

636 DBMS=xlsx REPLACE;

637 GETNAMES=YES;

638 RUN;

NOTE: The import data set has 429 observations and 32 variables.

NOTE: WORK.A data set was successfully created.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.13 seconds

cpu time 0.12 seconds

639

640 PROC SORT DATA=A;

641 BY GROUP DATE;

642 RUN;

NOTE: There were 429 observations read from the data set WORK.A.

NOTE: The data set WORK.A has 429 observations and 32 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

643

644 PROC CONTENTS;

645 RUN;

NOTE: PROCEDURE CONTENTS used (Total process time):

real time 0.03 seconds

cpu time 0.00 seconds

646

647 DATA B; SET A;

648 VAGINA1=INPUT(**bleep**, BEST.);

649 RF1=INPUT(RF, BEST.);

650 L_EAR1=INPUT(L_EAR, BEST.);

651 R_EAR1=INPUT(R_EAR, BEST.);

652 NOSE1=INPUT(NOSE, BEST.);

653 CHUTE_T1=INPUT (CHUTE_T, time.);

654 LATENCY1=INPUT (LATENCY, time.);

655 VOC1=INPUT(VOC, BEST.);

656 RUM1=INPUT(RUM, BEST.);

657 SNO1=INPUT(SNO, BEST.);

658 DIS1=INPUT(DIS, BEST.);

659 MOV1=INPUT(MOV, BEST.);

660 ESC1=INPUT(ESC, BEST.);

661 KICK1=INPUT(KICK, BEST.);

662 LOC1=INPUT(LOC, BEST.);

663 BL1=INPUT(BL, BEST.);

664 VEL_EXT1=INPUT(VEL_EXT, BEST.);

665 CORT1=INPUT(CORT, BEST.);

666 PEN1=INPUT(PEN, BEST.);

667 FS1=INPUT(FS, BEST.);

668 RUN;

NOTE: There were 429 observations read from the data set WORK.A.

NOTE: The data set WORK.B has 429 observations and 52 variables.

NOTE: DATA statement used (Total process time):

real time 0.07 seconds

cpu time 0.06 seconds

669

670 PROC CONTENTS;

671 RUN;

NOTE: PROCEDURE CONTENTS used (Total process time):

real time 0.03 seconds

cpu time 0.03 seconds

672

673 ods excel file='C:\Users\usuario\Desktop\Sheet2.xls';

674 ods excel options(sheet_interval="none");

675

676 PROC SORT DATA=B;

677 BY GROUP DATE;

678 RUN;

NOTE: There were 429 observations read from the data set WORK.B.

NOTE: The data set WORK.B has 429 observations and 52 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

679

680 PROC FREQ;

681 TABLE BALK_ENT VEL_ENT VOC1 RUM1 SNO1 DIS1 MOV1 ESC1 KICK1 LOC1 BL1 BALK_EXT VEL_EXT1 PEN1;

682 BY GROUP DATE;

683 RUN;

NOTE: There were 429 observations read from the data set WORK.B.

NOTE: PROCEDURE FREQ used (Total process time):

real time 2.13 seconds

cpu time 2.09 seconds

684

685 PROC MEANS n mean stderr;

686 VAR VAGINA1 RF1 L_EAR1 R_EAR1 NOSE1 CHUTE_T1 LATENCY1 BALK_ENT VEL_ENT VOC1 RUM1 SNO1 DIS1 MOV1

686! ESC1 KICK1 LOC1 BL1 BALK_EXT VEL_EXT1 CORT1 PEN1 FS1;

687 BY GROUP DATE;

688 RUN;

NOTE: There were 429 observations read from the data set WORK.B.

NOTE: PROCEDURE MEANS used (Total process time):

real time 0.52 seconds

cpu time 0.51 seconds

689

690 ods excel close;

NOTE: Writing EXCEL file: C:\Users\usuario\Desktop\Sheet2.xls

691

692

693 ods html close; /* close previous */

694 ods html; /* open new */

NOTE: Writing HTML Body file: sashtml11.htm

Thank you!!

Re: Time: Constant format to hhmmss format

PaigeMiller — Tue, 10 Mar 2020 23:21:32 GMT

It's not clear what you are seeing in SAS, or where you are seeing this. Can you show us a screen capture? Also, please show us a screen capture of what PROC CONTENTS says about these variables.

Re: Time: Constant format to hhmmss format

Reeza — Tue, 10 Mar 2020 23:48:15 GMT

Last one should have worked, please show the full code used.

Re: Time: Constant format to hhmmss format

Kurt_Bremser — Wed, 11 Mar 2020 05:35:53 GMT

Since you open a "workbook", does that mean the problem happens in Excel? Or do you you have it when you open a dataset in a library?

Re: Time: Constant format to hhmmss format

ballardw — Wed, 11 Mar 2020 14:30:26 GMT

One thing:

531  CHUTE_T1=INPUT (CHUTE_T, tod9.);
                              -----
                              48
532  LATENCY1=INPUT (LATENCY, tod9.);
                              -----
                              48
ERROR 48-59: The informat TOD was not found or could not be loaded.

TOD is a display Format, not an informat for reading data. If you have character data that looks like hh:mm:ss then you would want the TIME informat.

Re: Time: Constant format to hhmmss format

aska_ujita — Wed, 11 Mar 2020 15:07:43 GMT

Oh! All right, but even that... Because as I explained above, I changed to "time." but my results were not in hh:mm:ss format... Was a big number.

Re: Time: Constant format to hhmmss format

Reeza — Wed, 11 Mar 2020 16:02:54 GMT

You don't need INPUT all you likely need is a format.

data demo;
set a;

format chute_t  datetime.;

run;

Try that right after your import.

Please do not edit your post to update your question. There's no alert when that happens and it makes things seem out of line, add new information as a response.

Re: Time: Constant format to hhmmss format

aska_ujita — Wed, 11 Mar 2020 18:08:07 GMT

Still didn't work...

It doesn't recognize the datetime. or time. Just get error saying: The format $DATETIME was not found or could not be loaded.

I don't know why...

My imported data convert the values like this:

And my original data in the Excel Sheet are in hh:mm:ss format...

Than you!

Re: Time: Constant format to hhmmss format

Reeza — Wed, 11 Mar 2020 18:33:06 GMT

What's the type on the column? It's left aligned so it looks like it came in as a character for some reason. If you have any type of text in that column besides a header, this causes it to be read as a character.

Post the proc contents on the imported data. Basically the results from the following. And let us know which variables are time variables.

proc contents data=a;
run;

Re: Time: Constant format to hhmmss format

Kurt_Bremser — Thu, 12 Mar 2020 08:25:50 GMT

Since SAS complains about a missing character format (see the leading $), your variable is of type character, which is not usable for datetime values.

The core of your problem is that SAS has to guess variable types and attributes when importing directly from Excel files (since Excel does not have the concept of fixed attributes for columns), and one erroneous entry can wreck the whole process. Erroneous entries are a staple of Excel files, and all this makes Excel files the worst format imaginable for data transfer into SAS (and other similar applications). This is documented here on the communities in thousands of posts dealing with this problem.

The best remedy is to save the data from Excel to a text file (fixed width, delimited, csv) and read that with a data step, which gives you total control over types and attributes, and which also will throw NOTEs or ERRORs when invalid data is found in an input field, alerting you so you can deal with it.

Re: Time: Constant format to hhmmss format

aska_ujita — Thu, 12 Mar 2020 12:56:07 GMT

Hi!

Understood, yeah... maybe I should start to do it that way now...

In the end I transformed all my hh:mm:ss data in seconds and now it considered as number instead of character.

But thank you all for the support and help, at least I (and I am sure others) learned few things.

THANK YOU!

Kind regards, Aska.