Help using Base SAS procedures

ERROR: DBMS type EXCEL not valid for export

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

ERROR: DBMS type EXCEL not valid for export

Hello,

 

I tried to get my dataset to excel file, but it didn't work:

 

PROC EXPORT
 DATA=export
 DBMS=Excel5
 OUTFILE='path\datafile.xls'
 REPLACE;
RUN;

 

I also tried DMBS=excels and excel. I read, that there is a problem for SAS with the 64-bit 'Windows' operating system. I use SAS Base 9.4.

 

Did someone solve this problem?

 

BR, Silke

 


Accepted Solutions
Solution
‎02-17-2016 04:20 AM
Occasional Contributor
Posts: 7

Re: ERROR: DBMS type EXCEL not valid for export

Posted in reply to SMartin_82

1) you have large file and a memory error...

 

 

2) My understanding is that if proc setinit does not yield something like this:


---SAS/ACCESS Interface to PC Files

 

you cannot do DBMS=XLS or XLSX.

 

Probably then you cannot do DBMS=EXCELCS, either.

 

What if you were to use

 

DBMS=CSV

 

which EXCEL will read?

View solution in original post


All Replies
Super User
Posts: 19,770

Re: ERROR: DBMS type EXCEL not valid for export

Posted in reply to SMartin_82
Do you have a license for SAS/Access to PC Files? You should see it if you run the following:

proc setinit; run;
Occasional Contributor
Posts: 12

Re: ERROR: DBMS type EXCEL not valid for export

I don't think so...

 

Base SAS Software                    
SAS/STAT                             
SAS/GRAPH                            
SAS/Secure 168-bit                   
SAS/Secure Windows                   
SAS Enterprise Guide                 
SAS Workspace Server for Local Access
High Performance Suite               

Community Manager
Posts: 2,952

Re: ERROR: DBMS type EXCEL not valid for export

Posted in reply to SMartin_82

Try ODS EXCEL to create an XLSX file.  You need just Base SAS.  Here are some examples.

 

Chris

Occasional Contributor
Posts: 12

Re: ERROR: DBMS type EXCEL not valid for export

Posted in reply to ChrisHemedinger

I tried this:

 


ods excel file='path\test.xls';                                                                                                      
  proc print data=data;                                                                                                         
  run;                                                                                                                         
ods excel close;   
 

But I got out this warning and error messages:

WARNING: Unsupported device 'ACTIVEX' for EXCEL destination. Using device 'ACTXIMG'.
ERROR: The SAS System stopped processing this step because of insufficient memory.

Super User
Posts: 19,770

Re: ERROR: DBMS type EXCEL not valid for export

Posted in reply to SMartin_82

You don't appear to have a license to export Excel/Access files. It's probably worth chatting with whomever licenses SAS in your enterprise to obtain the license. 

 

A workaround is to use the macro here:

http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

Occasional Contributor
Posts: 7

Re: ERROR: DBMS type EXCEL not valid for export

[ Edited ]
Posted in reply to SMartin_82

what works for me is to use DBMS=XLSX

or in your case: DBMS=XLS

Occasional Contributor
Posts: 12

Re: ERROR: DBMS type EXCEL not valid for export

Posted in reply to johnsville

I tried DBMS=XLS but it didn't work too

Solution
‎02-17-2016 04:20 AM
Occasional Contributor
Posts: 7

Re: ERROR: DBMS type EXCEL not valid for export

Posted in reply to SMartin_82

1) you have large file and a memory error...

 

 

2) My understanding is that if proc setinit does not yield something like this:


---SAS/ACCESS Interface to PC Files

 

you cannot do DBMS=XLS or XLSX.

 

Probably then you cannot do DBMS=EXCELCS, either.

 

What if you were to use

 

DBMS=CSV

 

which EXCEL will read?

Occasional Contributor
Posts: 12

Re: ERROR: DBMS type EXCEL not valid for export

Posted in reply to johnsville

Hi,

 

I tried DBMS=CSV -> it works. But it didn't work with xls.

 

BR, Silke

Occasional Contributor
Posts: 7

Re: ERROR: DBMS type EXCEL not valid for export

Posted in reply to SMartin_82

I get the same warning: "WARNING: Unsupported device 'ACTIVEX' for EXCEL destination. Using device 'ACTXIMG'." but I do get an exported file.  I'm using ODS Excel with proc report on Base (local) SAS.  My reports seem to be fine but I have to be careful reading my log because sometimes I forget that other warnings may be masked by the one I know I see.

 

Does anyone know how to get ACTIVEX over AXTXIMG?  Or is it something our company hasn't purchased?  I'm on E.G 6.1 and SAS 9.4

 

 

Community Manager
Posts: 2,952

Re: ERROR: DBMS type EXCEL not valid for export

Posted in reply to TajisDawn

You can change your settings in Tools->Options->Results: Graphs to generate PNG instead of ActiveX -- that will eliminate the warning and give you a clean log.

Occasional Contributor
Posts: 7

Re: ERROR: DBMS type EXCEL not valid for export

Posted in reply to ChrisHemedinger

Thank you.  That worked.  Now the only warning occurs when I have more than 262 characters, which does occur at times but not always (diagnosis code list vary in length).

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 6267 views
  • 0 likes
  • 5 in conversation