University Edition dbsastype

[Vic]

Hi,

does anyone know by chance if the "DBSASTYPE"option is available in Universtity Edition? I try to 

LIBNAME DATA xlsx "&LINK/template.xlsx" ;
DATA work.aFile ;
SET DATA.'$INPUTDATA'n(dbsastype=(VAR3=numeric));
RUN;
LIBNAME DATA CLEAR;

but unfortunately my SAS does not recognize dbsastype? 

Anyone any idea?

Regards, Vic

[Patrick]

The DBSASTYPE is listed as a valid option in the SAS/Access 9.4 Interface to PC Files documentation and it doesn't really exclude the XLSX enginge.

https://documentation.sas.com/?docsetId=acpcref&docsetVersion=9.4&docsetTarget=p0w9ox0g4b3yphn1deuim...

However I believe DBSASTYPE is as the error message you're getting indicates not implemented for XLSX.

@Quentin already put in a suggestion for such an enhancement. Vote for it (click on big arrow on the left)

https://communities.sas.com/t5/SASware-Ballot-Ideas/Add-DBSASTYPE-or-similar-option-to-XLSX-engine-t...

[Vic]

Thank you very much for the hint. I will try tomorrow with SAS Base. In my version LIBNAME DATA EXCEL also does not work... 😞 Thank you

[Patrick]

---

@Vic wrote:

Thank you very much for the hint. I will try tomorrow with SAS Base. In my version LIBNAME DATA EXCEL also does not work... 😞 Thank you

---

I don't expect you'll get a different/better outcome with SAS Base. It's simply not implemented for XLSX

Interestingly when looking up the option in the manual SAS/Access 9.4 for Relational Databases then the supported databases get listed - and there isn't any Excel. ...but then there isn't also any MS Access and I believe the option would be working there.

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=acreldb&docsetTarget=n0v4...

Data source:	Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, Hadoop, HAWQ, Impala, Informix, JDBC, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, PostgreSQL, SAP HANA, SAP IQ, Teradata, Vertica
Notes:	Support for HAWQ was added in SAS 9.4M3.
Support for Amazon Redshift was added in the April 2016 release of SAS/ACCESS.
Support for JDBC was added in SAS Viya 3.4.

[Ksharp]

(dbsastype=(VAR3='num');

[Patrick · Accepted solution]

@Ksharp 

Keyword NUMERIC not it quotes is as per documentation

The error thrown when using DBSASTYPE with the XLSX engine is:

ERROR 22-7: Invalid option name DBSASTYPE.

[Vic]

Thank you all for your support. It should be LIBNAME EXCEL, then it works. Indeed the numeric format does not require any quotes. I cannot understand why SAS does not support LIBNAME XLSX. I must rewrite all my coding and change it. regards, vic

[gsh]

I have a same issue, I am reading my file with proc import as follow. Some of my variables have just missing values and they are getting converted into character, how could I stop it. I used dbsastype, it did not work out.

PROC IMPORT DATAFILE="/folders/myshortcuts/Myfolder/&var3/TissueTests (&var1).xlsx"
		    OUT=WORK.file&var1
		    DBMS=XLSX
		    REPLACE;
		    sheet="TissueTests (&var2)";
		    getnames=yes;
		    
RUN;

[Vic]

sorry for the delay in reply. The dbsastype works fine with my SAS Base at work, but I do not use the proc import. Unfortunally it seems that dbsastype is not supported in the University Edition of SAS. Regards, Vic

[Tom]

---

@Vic wrote:

sorry for the delay in reply. The dbsastype works fine with my SAS Base at work, but I do not use the proc import. Unfortunally it seems that dbsastype is not supported in the University Edition of SAS. Regards, Vic

---

The DBSASTYPE is not supportted for the XLSX engine.  You can use it with the EXCEL engine (and ORACLE, TERADATA, etc).

But the SAS/Universtity Edition does not support the EXCEL engine because that requires actually running Microsoft's Jet Engine software which is not available on the Unix virtual machine where SAS is running (or any Unix machine that I know of).

If you have messy data in Excel files then convert them to CSV files and write your own data step to read them.