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>
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.
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.
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 .
version=9,4
9.4 - that's good. Then the code I've already posted using ODS EXCEL should work for you. Give it a go.
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.
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
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;
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/
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
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).
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.