Importing excel files with hh:mm:ss data.

[NevermoreRedres]

Hello everyone! Hope you're all good!
I have this table with data in the hh:mm:ss format, as per the next image: 

I've used the following code to try to import it to SAS in the right format, but it dosen't work:

proc import datafile="/home/u58269001/sasuser.v94/análiseSVOI/Dados 2014 xlsx.xlsx" 
out=work.svoi2014 
dbms=xlsx replace;
sheet ="2014";
run;

data work.svoi2014_formatted;
    set work.svoi2014;
    format tobito--retiradafuneraria TIME8.; 
run;

This is the result i get:

Is there a way to import the file in the right format or i'll have to copy the data and use CARDS for it?

Thanks in advance!

[mkeintz]

Apparently Excel stores time values as fractions of a day,  where 06:00:00 displayed is stored as 0.25, 12:00:00 noon stored as 0.5, etc.  Those fractional values are what is brought by PROC IMPORT.

But SAS records time of day as number of seconds after midnight (06:00:00 is stored as 21600, noon is stored as 43200).  So you have to do two things after the proc import:

1. Multiply each value by 86400 (number of seconds in a day).
2. Reformat the variable using a TIME format.

data work.svoi2014_formatted;
    set work.svoi2014;
    array timvals {*}  tobito--retiradafuneraria;
    do i=1 to dim(timvals);
      timvals{i}=timvals{i}*86400;
    end;
    format tobito--retiradafuneraria TIME8.; 
    drop i;
run;

[NevermoreRedres]

Hey, mkeintz! Thanks for the answer!
I tried your code but this is what i get:

I also made sure that all the cells in the archive that are in fact in the hh:ss:mm format. I'll be leaving the file here for you guys to take a look on how it's formated.

This is the code i use when i do this in the CARDS tipe:

I really wish i can find a way to import the file already in the hh:mm:ss format because using the CARDS method gives me the need to check each line to see if the variable is in the right place.

Do you guys have any other ideas in how we can solve it?

[Tom · Accepted solution]

---

@NevermoreRedres wrote:

....

I also made sure that all the cells in the archive that are in fact in the hh:ss:mm format.

You failed to make all of the cells in each column have the same type (number or character) of data.

Let's look at the file you shared:

proc import file="C:\downloads\Dados 2014 xlsx.xlsx"
 dbms=xlsx out=test replace
;
run;
proc contents varnum;
run;

proc print data=test(obs=20);
run;

As you can only the first column has numbers.  The other columns all have at least one cell with a character string in it.

                           Variables in Creation Order

#    Variable             Type    Len    Format    Informat    Label

1    nnecropsia           Num       8    BEST.                 nnecropsia
2    tobito               Char     19    $19.      $19.        tobito
3    telaboracaobo        Char     19    $19.      $19.        telaboracaobo
4    chegadasvoi          Char     19    $19.      $19.        chegadasvoi
5    inicionecropsia      Char     17    $17.      $17.        inicionecropsia
6    retiradafuneraria    Char     19    $19.      $19.        retiradafuneraria

Which you can see by just looking at the first few observations:

 Obs    nnecropsia    tobito                 telaboracaobo          chegadasvoi            inicionecropsia      retiradafuneraria

   1         2        0.638888888888889      0.841666666666667      0.9375                 0.938888888888889    0.0319444444444444
   2         3        0.697916666666667      0.834027777777778      0.938888888888889      0.965277777777778    0.0319444444444444
   3         4        0.927777777777778      0.0847222222222222     0.157638888888889      0.344444444444444    0.429166666666667
   4         5        0.0333333333333333     0.148611111111111      0.207638888888889      0.375                0.452777777777778
   5         6        0.0277777777777778     0.226388888888889      0.289583333333333      0.395833333333333    0.454861111111111
   6         7        0.625                  0.625                  0.688888888888889      0.777777777777778    0.816666666666667
   7         8        0.958333333333333      0.0729166666666667     0.111805555555556      0.354166666666667    0.402083333333333
   8         9        0.458333333333333      0.654166666666667      0.7                    0.770833333333333    0.931944444444444
   9        10        *                      0.663888888888889      0.728472222222222      0.791666666666667    0.911805555555556
  10        11        0.520833333333333      0.663888888888889      0.728472222222222      0.8125               0.8625
  11        12        0.591666666666667      0.688888888888889      0.75625                0.833333333333333    0.935416666666667
  12        13        *                      0.851388888888889      0.929861111111111      0.934027777777778    0.400694444444444
  13        14        0.944444444444444      0.0784722222222222     0.138194444444444      0.354166666666667    0.425694444444445
  14        15        0.166666666666667      0.463194444444444      0.524305555555556      0.5625               0.617361111111111
  15        16        0.5625                 0.768055555555556      0.854166666666667      0.916666666666667    0.973611111111111
  16        17        0.395138888888889      0.43125                0.54375                0.65625              0.7875
  17        18        0.350694444444444      0.471527777777778      0.585416666666667      0.604166666666667    0.71875
  18        19        0.708333333333333      0.53125                0.602777777777778      0.625                0.327777777777778
  19        20        *                      *                      *                      *                    *
  20        21        0.357638888888889      0.421527777777778      0.484722222222222      0.489583333333333    0.543055555555556

If you want those strings converted into TIME values then you will need to first convert them into numbers and then multiply by 24 hours worth of seconds. Make sure to attach a display format so the values look like times to humans.

data want;
  set test;
  array old tobito telaboracaobo chegadasvoi inicionecropsia retiradafuneraria ;
  array time [5] ;
  format time: tod8.;
  do index=1 to dim(old);
    if old[index] ne '*' then time[index]='24:00't*input(old[index],32.); ;
  end;
  drop index tobito telaboracaobo chegadasvoi inicionecropsia retiradafuneraria ;
  rename time1=tobito time2=telaboracaobo time3=chegadasvoi
         time4=inicionecropsia time5=retiradafuneraria 
  ;
run;

Result

 Obs    nnecropsia      tobito    telaboracaobo    chegadasvoi    inicionecropsia    retiradafuneraria

   1         2        15:20:00      20:12:00        22:30:00         22:32:00            00:46:00
   2         3        16:45:00      20:01:00        22:32:00         23:10:00            00:46:00
   3         4        22:16:00      02:02:00        03:47:00         08:16:00            10:18:00
   4         5        00:48:00      03:34:00        04:59:00         09:00:00            10:52:00
   5         6        00:40:00      05:26:00        06:57:00         09:30:00            10:55:00
   6         7        15:00:00      15:00:00        16:32:00         18:40:00            19:36:00
   7         8        23:00:00      01:45:00        02:41:00         08:30:00            09:39:00
   8         9        11:00:00      15:42:00        16:48:00         18:30:00            22:22:00
   9        10               .      15:56:00        17:29:00         19:00:00            21:53:00
  10        11        12:30:00      15:56:00        17:29:00         19:30:00            20:42:00
  11        12        14:12:00      16:32:00        18:09:00         20:00:00            22:27:00
  12        13               .      20:26:00        22:19:00         22:25:00            09:37:00
  13        14        22:40:00      01:53:00        03:19:00         08:30:00            10:13:00
  14        15        04:00:00      11:07:00        12:35:00         13:30:00            14:49:00
  15        16        13:30:00      18:26:00        20:30:00         22:00:00            23:22:00
  16        17        09:29:00      10:21:00        13:03:00         15:45:00            18:54:00
  17        18        08:25:00      11:19:00        14:03:00         14:30:00            17:15:00
  18        19        17:00:00      12:45:00        14:28:00         15:00:00            07:52:00
  19        20               .             .               .                .                   .
  20        21        08:35:00      10:07:00        11:38:00         11:45:00            13:02:00

[NevermoreRedres]

Hey, Tom!

Your code worked wonders. I don't know why the columns didn't came as numbers, cause i'm sure i formated then as hh:mm:ss in the file. I'll investigate it further.

Thank you, Tom, and everybody who helped!

[Tom]

If the cell in the XLSX file has a * in it then it is NOT a numeric cell.

[NevermoreRedres]

Thanks, Tom!
What should i use instead of * for blank spaces?

[Tom]

Leave it empty.

[Ksharp]

Try to import them all in CHARACTER type.

proc import datafile="/home/u58269001/sasuser.v94/análiseSVOI/Dados 2014 xlsx.xlsx" 
out=work.svoi2014 
dbms=excel replace;
dbdsopts="dbsastype=(tobito='char(80)'  telaboracaobo='char(80)')";
run;

[Patrick]

Hmmm.... This works for me with SAS9.4M8 under Windows.

How are your Excel cells formatted?

                        

[Ksharp]

Patrick,

Try to test this kind of data.

Put format h:mm:ss on C3 and C4 ,but not C2 .