Exporting Excel file from SAS with individual sheet per dataset

[Patrick]

@CamRutherford

ODS EXCEL -  give me a sec

SAS version 6.1?!? That must be the EG client version and not the version of the SAS server.

Please run the following

proc setinit;run;

And then post what you find in the log under:

Current version: <and here the version>

[Patrick]

@CamRutherford

For ODS EXCEL basically the same code than what you've posted (see below). This will only work with a current version of SAS.

DATA PRDSALE;
     SET SASHELP.PRDSALE;
     DIFFERENCE = ACTUAL-PREDICT;
  RUN;
PROC SORT DATA=PRDSALE; 
BY COUNTRY REGION DIVISION YEAR; 
RUN; 
QUIT;

ods excel file='c:\temp\test.xlsx' options(sheet_interval="bygroup");

proc report data=PRDSALE;
  by country;
  where year=1993;
run;
ods excel close;

I could replicate the issue you've raised with ODS TAGSET.EXCELXP. Things work for me without issues when using ODS EXCEL.

[CamRutherford]

@Patrick

Yes its Enterprise Guide.

I don't have anything that says 'current version'

[Patrick]

@CamRutherford

You need to run the following code out of EG in a program window.

proc setinit;run;

Then check the log. That will give you the version of SAS on the server where your SAS code executes. 

SAS EG is only the client. What SAS syntax will work depends on the version of your SAS server which executes the code and not on the version of the client.

[CamRutherford]

@Patrick

Thanks.

I don't understand how I can run the below code and then able to download the excel file and view perctly fine but then when sending the email it doesn't like the file I have and tells me it's corrupt in all different file extensions.

DATA PRDSALE;
     SET SASHELP.PRDSALE;
     DIFFERENCE = ACTUAL-PREDICT;
  RUN;
PROC SORT DATA=PRDSALE; 
BY COUNTRY REGION DIVISION YEAR; 
RUN; 
QUIT;

ods tagsets.excelxp file='test.xml' options(sheet_interval="bygroup");

proc report data=PRDSALE;
  by country;
  where year=1993;
run;
ods tagsets.excelxp close;
    
 
FILENAME SEND EMAIL                                                                              
     TO      =  'cameron.rutherford@rci.com'                                  
     SUBJECT =  'MULTI-SHEET'                             
	attach=("test.xml");                       
     ;                                                              
  DATA _NULL_;                                                      
     FILE SEND ;                                                    
     PUT 'PLEASE RECEIVE THE ATTACHED. -- REGARDS,';                
RUN ;          

I've just ran ...

<span class="token procnames">proc</span> setinit<span class="token punctuation">;</span><span class="token procnames">run</span><span class="token punctuation">;</span>

 and I get this...

proc setinit;run;

NOTE: PROCEDURE SETINIT used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              339.65k
      OS Memory           35992.00k
      Timestamp           05/31/2017 07:00:05 AM
      Step Count                        83  Switch Count  18
      Page Faults                       0
      Page Reclaims                     62
      Page Swaps                        0
      Voluntary Context Switches        35
      Involuntary Context Switches      9
      Block Input Operations            544
      Block Output Operations           0
      
Original site validation data
Site name:    'SITE XXXXX'.
Site number:  XXXXX.
Expiration:   29JUN2017.
Grace Period:  45 days (ending 13AUG2017).
Warning Period: 45 days (ending 27SEP2017).
System birthday:   26JUL2016.
Operating System:   LIN X64 .

[CamRutherford]

version=9,4

[Patrick]

@CamRutherford

9.4 - that's good. Then the code I've already posted using ODS EXCEL should work for you. Give it a go. 

[CamRutherford]

@Patrick

Nice one thanks!

Although when I run your code below...

I get the following error when trying to download the file = 'Unable to open the external viewer. There is no application associated with the '.sge' file type.

[Patrick]

@CamRutherford

Sorry but I don't know where such a .sge file would be coming from.

Things work for me in a Windows environment and with SAS EG 7.13 HF5

[CamRutherford]

Can I not do something like this...

PROC EXPORT DATA= Sashelp.Class outfile= "c:\temp\want.xlsx " dbms=xlsx replace;
sheet="Class";
PROC EXPORT DATA=sashelp.cars outfile="c:\temp\want.xlsx" dbms=xlsx; 
sheet="Car";
run; 

[Patrick]

@CamRutherford

That's another option which is totally o.k. to use.

With SAS 9.4 the preferred options are EXCEL for ODS and the XLSX engine for anything else.

ODS EXCEL is missing in the following Blog as it's newer than the blog. It's still very worthwhile reading it.

http://blogs.sas.com/content/sasdummy/2012/02/11/export-excel-methods/ 

[CamRutherford]

@Patrick

Thank you for all your help.

I'm now trying this route but I'm still getting messages saying the file is corrupt in my email attachment but I'm sure I will figure it out.

data class;
set sashelp.class;
run;
data cars;
set sashelp.cars;
run;

PROC EXPORT 
DATA= class 
outfile= "/user/&user/test.xlsx" 
dbms=xlsx replace;
sheet="Class";
PROC EXPORT 
DATA= cars 
outfile="/user/&user/test.xls" 
dbms=xls; 
sheet="Car";
run; 

FILENAME SEND EMAIL                                                                              
     TO      =  'cameron@gmail.com'                                  
     SUBJECT =  'MULTI-SHEET'                             
	attach=("test.xls");                       
     ;                                                              
  DATA _NULL_;                                                      
     FILE SEND ;                                                    
     PUT 'PLEASE RECEIVE THE ATTACHED. -- REGARDS,';                
RUN ; 

Cheers

[Patrick]

@CamRutherford

PROC Export will create the .xlsx for you if it doesn't exist but it won't delete and recreate it if it's already there.

I suggest you first manually delete your .xlsx and then re-run your code and check if you still encounter the "corrupt" issue.

You might also want to add some code at the beginning of your process which removes the .xlsx in case it should already exist from a previous run (that's what the X command did in the sample codes I've posted; you would have to amend the code to your Linux environment). 

[RW9]

Have you tried doing the libname type I posted above, this creates a native .xlsx file?  As you have 9.4 that should work fine.

Also, your code:

PROC EXPORT 
DATA= class 
outfile= "/user/&user/test.xlsx" 
dbms=xlsx replace;
sheet="Class";
PROC EXPORT 
DATA= cars 
outfile="/user/&user/test.xls" 
dbms=xls; 
sheet="Car";
run; 

You are outputting to first xlsx and second to xls.