Libname XLSX reading in date var that is almost always missing

[Walternate]

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?

[Reeza]

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

[SASKiwi]

Try the DBSASTYPE option:

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

[Quentin]

---

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

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

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

[SASKiwi]

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

[Kurt_Bremser]

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.