topic Re: Libname XLSX reading in date var that is almost always missing in SAS Programming

Libname XLSX reading in date var that is almost always missing

Walternate — Thu, 20 Oct 2022 21:09:23 GMT

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?

Re: Libname XLSX reading in date var that is almost always missing

Reeza — Thu, 20 Oct 2022 21:20:18 GMT

Not really, except to check the type and convert it after the fact.

Re: Libname XLSX reading in date var that is almost always missing

SASKiwi — Thu, 20 Oct 2022 21:29:04 GMT

Try the DBSASTYPE option:

data want;
  set xl.mytab (dbsastype=(MyExcelDate = 'date'));
run;

Re: Libname XLSX reading in date var that is almost always missing

Quentin — Thu, 20 Oct 2022 21:37:31 GMT

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

https://communities.sas.com/t5/SASware-Ballot-Ideas/Add-DBSASTYPE-or-similar-option-to-XLSX-engine-to-force-variable/idi-p/453938

See also this related thread, where people shared workarounds, for example changing to the PCFILES engine or another engine which does support dbsastype:

https://communities.sas.com/t5/SAS-Programming/Reading-XLSX-file-and-force-SAS-column-types/m-p/430934#M106536

I'm a big fan of the XLSX engine. The fact that dbsastype is unsupported is my only complaint.

Re: Libname XLSX reading in date var that is almost always missing

SASKiwi — Thu, 20 Oct 2022 22:52:16 GMT

@Quentin - Thanks for pointing that out. Maybe it works for the EXCEL engine? I don't have M7 to try.

Re: Libname XLSX reading in date var that is almost always missing

Kurt_Bremser — Fri, 21 Oct 2022 06:48:08 GMT

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.