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!!!
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.
In attachments the file.
thanks
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;
Have you tried using libname instead of proc import? Have you tried replacing the format after importing?
with libnam is the same problem:
libname xls "C:\Users\ui43925\Desktop\example.xlsx";
convert datatime9. in timestamt isn't possibile (lenght 8):
Use XLSX or EXCEL as the libname engine, not Xls.
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
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.
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
*/
@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.
Sorry, but i don't use R.
Thanks
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.