BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Vic
Fluorite | Level 6 Vic
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@Ksharp 

Keyword NUMERIC not it quotes is as per documentation

Capture.JPG

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

ERROR 22-7: Invalid option name DBSASTYPE.

 

View solution in original post

9 REPLIES 9
Patrick
Opal | Level 21

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
Fluorite | Level 6 Vic
Fluorite | Level 6

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
Opal | Level 21

@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
Super User
(dbsastype=(VAR3='num');
Patrick
Opal | Level 21

@Ksharp 

Keyword NUMERIC not it quotes is as per documentation

Capture.JPG

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

ERROR 22-7: Invalid option name DBSASTYPE.

 

Vic
Fluorite | Level 6 Vic
Fluorite | Level 6

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
Calcite | Level 5 gsh
Calcite | Level 5

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
Fluorite | Level 6 Vic
Fluorite | Level 6

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
Super User Tom
Super User

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

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 2542 views
  • 3 likes
  • 5 in conversation