topic Betreff: proc import (problem format) in SAS Programming

proc import (problem format)

Cello23 — Fri, 08 Jul 2016 08:59:45 GMT

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

Re: proc import (problem format)

Cello23 — Fri, 08 Jul 2016 09:13:38 GMT

In attachments the file.

thanks

Betreff: proc import (problem format)

andreas_lds — Fri, 08 Jul 2016 09:18:19 GMT

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

Betreff: proc import (problem format)

Cello23 — Fri, 08 Jul 2016 09:38:27 GMT

with libnam is the same problem:

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

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

Betreff: proc import (problem format)

Reeza — Fri, 08 Jul 2016 12:10:35 GMT

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

Betreff: proc import (problem format)

andreas_lds — Fri, 08 Jul 2016 12:18:44 GMT

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

Betreff: proc import (problem format)

Cello23 — Fri, 08 Jul 2016 12:20:16 GMT

😞
the same problem.

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

Betreff: proc import (problem format)

Cello23 — Fri, 08 Jul 2016 12:25:22 GMT

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

Betreff: proc import (problem format)

Reeza — Fri, 08 Jul 2016 12:37:20 GMT

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.

Betreff: proc import (problem format)

Cello23 — Fri, 08 Jul 2016 13:00:04 GMT

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;

Betreff: proc import (problem format)

rogerjdeangelis — Fri, 08 Jul 2016 23:06:02 GMT

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
*/

Betreff: proc import (problem format)

Reeza — Fri, 08 Jul 2016 23:08:29 GMT

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

Betreff: proc import (problem format)

rogerjdeangelis — Fri, 08 Jul 2016 23:33:36 GMT

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;

Betreff: proc import (problem format)

Reeza — Fri, 08 Jul 2016 23:39:52 GMT

A lot of companies won't allow R. The free potentially untested packages are security risks when dealing with confidential information. Also, the one my company sites all the time, if there's no support then it can't be purchased as its a risk.

Besides the IML license on top of SAS licence and renewal costs are trivial after initial purchase.

Re: proc import (problem format)

Tom — Sat, 09 Jul 2016 00:44:52 GMT

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;

Betreff: proc import (problem format)

Cello23 — Mon, 11 Jul 2016 07:46:45 GMT

Sorry, but i don't use R.

Thanks