Time: Constant format to hhmmss format

[aska_ujita]

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.);

                              -----

                              48

532  LATENCY1=INPUT (LATENCY, tod9.);

                              -----

                              48

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!!

[PaigeMiller]

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.

[Reeza]

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

[Kurt_Bremser]

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?

[ballardw]

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.

[aska_ujita]

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.

[Reeza]

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.

[aska_ujita]

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!

[Reeza]

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;

[Kurt_Bremser]

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.

[aska_ujita · Accepted solution]

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.