proc import (problem format)

[Cello23]

Hi,

i would import a file excel but i have a problem:

PROC IMPORT OUT= WORK.example   
DATAFILE= "C:\Users\ui43925\Desktop\example.XLSX"
            DBMS=EXCEL REPLACE;
     SHEET="example"; 
     GETNAMES=YES;
     MIXED=YES;
     SCANTEXT=YES;
     USEDATE=YES;
     SCANTIME=YES;
RUN;

After import, in the table sas the column "Ora format" and "Hour" isn't format timestamt but in DATE9., what is my fault ?

Thanks!!!

[Cello23]

In attachments the file.

thanks

[Tom]

What version of SAS are you running? I was able to read that file using SAS 9.4 using the XLSX engine without any issue.

%let path=C:\downloads;
libname x xlsx "&path/example.xlsx";
proc print data=x.example (obs=10); run;

[andreas_lds]

Have you tried using libname instead of proc import? Have you tried replacing the format after importing?

[Cello23]

with libnam is the same problem:

libname xls "C:\Users\ui43925\Desktop\example.xlsx";

convert datatime9. in timestamt isn't possibile (lenght 8):

[Reeza]

Use XLSX or EXCEL as the libname engine, not Xls. 

[Cello23]

😞
the same problem.

libname prova excel "C:\Users\ui43925\Desktop\example.xlsx";

[andreas_lds]

Length eight is max for numeric variables and always sufficient for datetime variables.

I have no idea why proc impot does not recognize the datetime as datetime - could be a bug.

Easiest way to solve the problem: don't use excel as data-source

[Cello23]

you're right , but i receive this file every day with email attachment, automatically save it with outlook to run query in SAS. I would like to not have manually activities ...

[Reeza · Accepted solution]

If XLSX doesn't work you can look into DBSASTYPE but your options are limited. 

You'll either need to convert to CSV, which can be automared, or accept a date variable. 

[Cello23]

Fantastic!!! thanks so much!!!

PROC IMPORT OUT= WORK.example
DATAFILE= "C:\Users\ui43925\Desktop\example.XLSX"
DBMS=EXCEL REPLACE;
SHEET="example";
GETNAMES=YES;
MIXED=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
DBDSOPTS=" SASDATEFMT=('Hour'='DATETIME27.' 'Ora format'='DATETIME27.') ";
RUN;

[rogerjdeangelis]

R seemed to hadle it very naturally. 

Note I could easily reformat the datetime display with a format, like SAS.
I think the defalt format is either POSIX or ISO.

The main issue is creating a SAS dataset and there is work going on to do this.
R can read SAS datasets.

%utl_submit_r64(
  library(XLConnect);
  wb <- loadWorkbook('d:/xls/example.xlsx');
  data <- readWorksheet(wb, sheet = 'example');
  head(data);
);

/*
           Ora.format                Hour
1 2016-05-29 00:00:00 2016-05-29 00:00:00
2 2016-05-29 00:00:00 2016-05-29 00:00:00
3 2016-05-29 01:00:00 2016-05-29 01:00:00
4 2016-05-29 01:00:00 2016-05-29 01:00:00
5 2016-05-29 02:00:00 2016-05-29 02:00:00
*/

[Reeza]

@rogerjdeangelis But this isn't an R forum? 🤔 I have nothing against R and even use it for consulting but not sure how answer above is relevant given question.  

[Cello23]

Sorry, but i don't use R.

Thanks

[rogerjdeangelis]

Here is a solution the OP can use easily, I just don't see the need for IML and R.

proc iml;
  submit / R;
    library(XLConnect)
    wb <- loadWorkbook('d:/xls/example.xlsx')
    data <- readWorksheet(wb, sheet = 'example')
  endsubmit;
  call importdatasetfromr('data','data');
quit;