topic Re: Excel libname dbsastype error Invalid option in SAS Programming

Excel libname dbsastype error Invalid option

DebG — Tue, 04 Jul 2023 15:57:56 GMT

I am trying to set the data types when reading a spreadsheet using the libname XLSX enging.

I am sucssful in reading the spreadsheet, however when I try to use the DBSASTYPE SAS throws and error. I am using SAS EG 8.3. The facility_id appears in excel to be a column of numbers. I am able to read it and convert it.

My question is specific to using the dbSASTYPE functionality. If I remove the dbSAStype, I can successfully read the file.

libname xl XLSX "/sasdata/airb_output01/dgear/cc_basel/my_file.xlsx" ;

data cc_basel1;
set xl.GUI(DBSASTYPE=(FACILITY_ID='char(20)'));
run;

Results in:

34 data cc_basel1;
35 set xl.GUI(DBSASTYPE=(FACILITY_ID='char(20)'));
_________
22
ERROR 22-7: Invalid option name DBSASTYPE.

Is there something that must be installed?

Re: Excel libname dbsastype error Invalid option

Reeza — Tue, 04 Jul 2023 16:59:48 GMT

No, this option isn't enabled for the XLSX engine. As far as I know there's no way to specify the type 100% fool proof from an Excel file except by converting to CSV and reading that in.

Re: Excel libname dbsastype error Invalid option

Quentin — Tue, 04 Jul 2023 21:14:45 GMT

Unfortunately, as Reeza mentioned, the XLSX engine does not have support for the DBSASTYPE option.

Please upvote this ballot item, where I suggested this improvement:

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

I did notice on LinkedIN a couple weeks ago that Henry Feldman is 'open to work' after departing ATT. Perhaps SAS will be able to hire him back... I wouldn't think it would be hard to add support for DBSASTYPE.