Hi,
I am reading in a bunch of Excel files using the XLSX libname:
For each Excel file, the macro does this:
libname xl xlsx "mypath\file1.xlsx";
data want;
set xl.mytab;
run;
The problem is that there is a date variable on each Excel file. This is almost always missing so SAS is interpreting it as a character variable. I tried adding format and length statements, which causes an error message because there is a mismatch between what I'm telling SAS the variable is and the decision SAS has made by checking the values in the Excel file. Specifiically: Variable has been defined as both character and numeric
Are there any workarounds for this?
Try the DBSASTYPE option:
data want;
set xl.mytab (dbsastype=(MyExcelDate = 'date'));
run;
@SASKiwi wrote:
Try the DBSASTYPE option:
data want; set xl.mytab (dbsastype=(MyExcelDate = 'date')); run;
Last time I checked, dbsastype was not supported by the XLSX engine, unfortunately. I'm not sure I checked in M7. Would be thrilled if it's there. Or maybe it will be a gift in M8....
@Walternate please consider upvoting this ballot item requesting SAS add support for the dbsastype option to the XLSX engine:
See also this related thread, where people shared workarounds, for example changing to the PCFILES engine or another engine which does support dbsastype:
I'm a big fan of the XLSX engine. The fact that dbsastype is unsupported is my only complaint.
@Quentin - Thanks for pointing that out. Maybe it works for the EXCEL engine? I don't have M7 to try.
The usual advice:
save the data to a sensible file format (e.g. csv) and read it with a data step, where you have full control.
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.