DATA Step, Macro, Functions and more

proc import (problem format)

Accepted Solution Solved
Reply
Contributor
Posts: 51
Accepted Solution

proc import (problem format)

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;

problem.PNG

 

 

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

Thanks!!!

 


Accepted Solutions
Solution
‎07-08-2016 08:59 AM
Super User
Posts: 19,851

Betreff: proc import (problem format)

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. 

View solution in original post


All Replies
Contributor
Posts: 51

Re: proc import (problem format)

In attachments the file.

thanks

Super User
Super User
Posts: 7,074

Re: proc import (problem format)

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;

 

Capture.JPG

Super Contributor
Posts: 345

Betreff: proc import (problem format)

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

Contributor
Posts: 51

Betreff: proc import (problem format)

Posted in reply to andreas_lds

with libnam is the same problem:

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

 

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

errore_2.JPG

 

Smiley Sad

Super User
Posts: 19,851

Betreff: proc import (problem format)

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

Contributor
Posts: 51

Betreff: proc import (problem format)

:-(
the same problem.

libname prova excel "C:\Users\ui43925\Desktop\example.xlsx";
Super Contributor
Posts: 345

Betreff: proc import (problem format)

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 Smiley Wink

Contributor
Posts: 51

Betreff: proc import (problem format)

Posted in reply to andreas_lds
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 ...
Solution
‎07-08-2016 08:59 AM
Super User
Posts: 19,851

Betreff: proc import (problem format)

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. 

Contributor
Posts: 51

Betreff: proc import (problem format)

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;
Valued Guide
Posts: 505

Betreff: proc import (problem format)

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 */
Super User
Posts: 19,851

Betreff: proc import (problem format)

Posted in reply to rogerjdeangelis

@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.  

Contributor
Posts: 51

Betreff: proc import (problem format)

Posted in reply to rogerjdeangelis

Sorry, but i don't use R.

Thanks

Valued Guide
Posts: 505

Betreff: proc import (problem format)

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 15 replies
  • 892 views
  • 7 likes
  • 5 in conversation