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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.