Help using Base SAS procedures

Proc Export XLSX

Reply
Super Contributor
Posts: 400

Proc Export XLSX

I have a process through my website that calls a sas macro which creates my data set and does a proc export.  It is currently working creating an xls file, but based on the row limit I want to export xlsx files.  When I changed my proc export it doesn't work correctly.  I don't get an error but the file is empty and the sheet name is a bunch of letters and numbers.  On my test server it does work but i'm not sure what settings I might be missing.    If I run the macro directly from sas it does create the xlsx correctly but running from my website it does not.

This is my proc export

PROC EXPORT DATA = WORK.&fname

            OUTFILE= "&Path.\&file2..xlsx"

            DBMS=EXCEL REPLACE ;

RUN ;

Thank you

SAS Super FREQ
Posts: 8,779

Proc Export XLSX

Hi:

  You may want to work with Tech Support on this. Depending on what version of SAS you have on your server and what platform you have (Unix, Windows 64, Windows 32) and some other factors, you may need to use the EXCELCS engine or the PC File Server on your server machine. Also, if your server is a platform, such as UNIX or Linux, which doesn't have Office, you may need to alter your code so that a connection is made to a different machine when the file is created.

cynthia

Super Contributor
Posts: 400

Re: Proc Export XLSX

Cynthia,


Thank you for your reply

The server i'm running on is a windows server 2003.  This is a virtual server. 

The sas version i'm running is 9.2 (TS2M0).

I have a different portion of my website that allows users to select data and export in excel(xlsx) and that works.

Could it be some type of permissions issue for my web user account?

If I change it back to xls and excel2000 it works fine.

The tab name of the xlsx file has all different letters and numbers.

If I run my program on the server through sas it works fine. 

Thank you for any help you can give me.

SAS Super FREQ
Posts: 8,779

Proc Export XLSX

Hi:

  I'd really recommend working with Tech Support on this one. I think you need a different version of 9.2 (either M2 or M3) in order to create XLSX files. The info on the tab name is irrelevant to this issue. The fact that your code works on some instances and not in others means that Tech Support will have to look at the underlying code and perhaps have you get some diagnostic information to help you figure out what is going on.

cynthia

Super Contributor
Posts: 400

Re: Proc Export XLSX

Cynthia,

Thanks for the help.  I will have to exhaust all my tests first.  My lead thinks it's a settings issue.

I did create a test page and pointed it to a regular sas program that creates 100,000 rows and exports it to an xlsx fine.  There is some setting I think i'm missing.

I have also tried the following within my macro and it is still blank even though the log says it has 42,000 rows

LIBNAME xdb EXCEL PATH="&out.\&file..xlsx" ;

data xdb.&name ;

    set WORK.&name ;

run ;

If I change the xlsx to xls it works fine.  What is causing sas not to create that file through a macro but it can create an xlsx file through a sas program?

Thank you again I do appreciate your help.

SAS Super FREQ
Posts: 8,779

Proc Export XLSX

Hi:

  As far as I know, the EXCEL engine does not EXPORT to an XLSX format. This Tech Support note: http://support.sas.com/kb/32/455.html explains that:

"The EXCEL and XLS engines do not support importing and exporting of Microsoft Office 2007 files. These files have an extension of .xlsx, which is based on the Open XML standard. This type of file also allows for 16,384 columns and 1,048,576 rows.

Support for Office 2007 files is available in SAS® 9.2 Phase 2 (TS2M0). Contact SAS Customer Service, your account team, or SAS Technical Support to request the Phase 2 software."

  So, if you do not have the Phase 2 versio of SAS 9.2, I am not sure how it's working sometimes and not others. A .XLS file is a proprietary, binary Excel format. An .XLSX file is not really 1 file, it is a compressed archive file that is stored in Microsoft format. They are two different Microsoft formats and they are NOT interchangeable.

  You can prove this to yourself by taking SAS out of the picture. Go into Excel, type some numbers into a worksheet and then save the worksheet as an XLSX format file. Close Excel. Then go to Windows Explorer and rename the file extension from .XLSX to .ZIP and then open the zip file with WinZip to look inside. You will see that an XLSX file is a collection of related files.

  I still recommend working with Tech Support especially if you do NOT have Phase 2 version of SAS 9.2.

cynthia

Super Contributor
Posts: 400

Re: Proc Export XLSX

Cynthia,

Thank you again for all your help trying to get this figured out.  I am running  SAS 9.2 (TS2M0).

I had to jump on a different project and just started looking at this issue again.

Is there any known issue of doing a proc export within a macro for an xlsx file? 

SAS Super FREQ
Posts: 8,779

Proc Export XLSX

Hi,

I am 99.999% certain that the issue is not with the Macro facility. Before you "macro-ize" anything, you need to have working SAS code. So, based on what you say, it sounds like you can, under some circumstances, write to an xlsx file using PROC EXPORT. I assume that you are NOT using DBMS=EXCEL for this code.

Adding macro into the mix would only be an issue, I think if there was some file--close--timing issue. But I still feel, strongly, that this is something you should work on with Tech Support.

Without seeing all your code and knowing more about your operating system,  it is only speculation about what might be causing the problem. And, Tech Support can replicate your 32/64 bit operating system, etc and replicate your version of SAS to see if they can observe the same behavior.

cynthia

Super Contributor
Posts: 400

Re: Proc Export XLSX

Cynthia,

Thank you so much for your help and patience. 

I did find some interesting things.

When I user selects they want their output in excel my macro includes a program that has this proc export:

proc export data = work.&fname

            outfile= "&path.\&file2..xlsx"

            dbms=excel replace ;

run ;

if I run it like this it doesn't work.  If I add (obs=25059) after data it works.  If I change this to (obs=25060) it stops working.

I also tried to just create 1 variable with 110,000 rows.

data temp ;

    do i = 1 to 110000 ;

        output ;

    end ;   

run ;

proc export data = work.temp

            outfile= "d:\temp\temp.xlsx"

            dbms=excel replace ;

run ;

This will work but if I change it to 115,000 it stops working again. 

I get an empty xlsx file with both and a tab name with letters and numbers.

Is there a file size limit?  Am I using the wrong dbms?

Thank you so much for your help

SAS Super FREQ
Posts: 8,779

Proc Export XLSX

Hi,

  Really, really, not sure what's going on. But DMBS=EXCEL is probably the wrong DBMS engine to use. Again, depending on a lot of factors, you really should work with Tech Support on this. I think that you are trying a lot of things that are giving inconsistent results because of system or engine problems. Tech Support really has the expertise to help you. Really.

cynthia

Super Contributor
Posts: 400

Proc Export XLSX

Cynthia,

Thank you for your reply.

I understand what you are saying.  There are many variables at play that are giving me inconsistant results.  I will have to ask my lead next wee if I can contact tech support again. 

Its weird that if I run the original program through SAS it works fine but through the web it does and doesn't.  But all the web page does is make a call to a sas program.  The program is what creates the data and the xlsx file. 

I'm just curious if DBMS=EXCEL is the wrong one which other one would you guess it could be? 

Thanks again for the quick replies.  In the situation i'm dealing with it is much appreciated.

SAS Super FREQ
Posts: 8,779

Proc Export XLSX

Hi,

I think EXCELCS or PCFILES for 9.2 and XLS or XLSX for 9.3

http://support.sas.com/documentation/cdl/en/acpcref/63181/HTML/default/viewer.htm#n0msy4hy1so0ren1ac...

And that's why I keep recommending Tech Support, the engines used for creating/accessing proprietary files like .xlsx and .xlsb etc changed between 9.2 and 9.3.

cynthia

Super Contributor
Posts: 400

Proc Export XLSX

Cynthia,

Thank you. 

I will discuss this for sure with my lead next week.  I don't want to spend more time just testing what will and won't work.  I need to find a solution and I think tech support will be the only way.

I am running on 9.2 TS2M0 and I did try using EXCELCS and PCFILES and both gave me DBMS type EXCELCS not valid for export.

Using the libname way to export goes through the same engine correct?  I'm going to look to see if there is other avenues to create the file till we contact tech support.

Thank you again


Super Contributor
Posts: 400

Proc Export XLSX

Cynthia,

I did some more research on this issue and it looks like others have had this issue but using MS Project 2010.

http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/961fa23c-fc03-4cae-a9e4-...

It seems to be if the file is going to be greater then 1mb is when I get the blank xlsx file.  My web page is not running sas all my web page does is collect parameters then it starts sas on our server and runs the sas program.  At that point SAS is doing all the data work and then creating the xlsx file.

Thank you

SAS Super FREQ
Posts: 8,779

Proc Export XLSX

Hi:

  Everything I read on that site looked like it referred to a custom .NET type of application using Microsoft Technology and the Microsoft KB article discussed using ASP and server-side technology.

  My inclination would still be to verify, with Tech Support, that the issue you found is relevant to PROC EXPORT and the methods you're using to create your output file.

cynthia

Ask a Question
Discussion stats
  • 14 replies
  • 9719 views
  • 1 like
  • 2 in conversation