I recently wrote about my foray into the experimental world of ODS EXCEL. Using this new destination in SAS 9.4, I was able to create multisheet workbooks -- containing reports and graphs -- in the XLSX format. I outlined some of the benefits -- such as being part of Base SAS and its ability to create native XLSX files. But I also pointed out some of the shortcomings that I experienced, such as slow performance with large amounts of data.
I'm working on a consulting project in which I needed to provide my client with an Excel-based report. This report requires an easy-to-read summary sheet, such as might be created with PROC MEANS and PROC TABULATE. That's a perfect job for ODS EXCEL. But I also needed to include subsequent sheets that contained different versions of detailed data -- tables with lots of columns and many thousands of rows. In my experience, this can bog down ODS EXCEL; I find that PROC EXPORT with DBMS=XLSX performs much better. So...I decided to see if I could combine the two approaches to create a single file.
The process worked perfectly for my purposes. It relies on the following behaviors/assumptions:
- ODS EXCEL creates a new XLSX file. That's okay, because each time I run my process I want to replace the XLSX that I had before. If I wanted to retain previous versions, I could tack a date suffix onto the target file name.
- PROC EXPORT with DBMS=XLSX can add/update a sheet within an existing XLSX file. That's a relatively recent feature, added in SAS 9.4 (and perhaps worked in SAS 9.3M2). This means that I can create the file with ODS EXCEL, then update the same file using PROC EXPORT, all within a single SAS program. (Remember, PROC EXPORT with DBMS=XLSX requires SAS/ACCESS to PC Files.)
I would like to show an example of the output, but I'm sure that my customer wouldn't approve of me featuring their data details on my blog. So instead I'll risk alienating a different group of acquaintances: my Facebook friends.
Years ago I built an app that turns your Facebook friend data into a SAS program. Even though eons have passed (in social media time), the app still works. (You can try it yourself: it runs as a custom task in SAS Enterprise Guide or as a standalone program on your Windows PC.) I ran it just now to gather my latest Facebook friend data and create SAS data sets with my friend details. Then I used ODS EXCEL to create a summary sheet, and PROC EXPORT to create several detailed sheets. Here's an example of the summary:
And then one of the detailed sheets, produced by PROC EXPORT of one of the detailed data sets my Facebook program created:
Here's the SAS program that produces this output. First, I run the program that is generated by my Facebook app. Then I follow up with this program that generates the Microsoft Excel spreadsheet file.
/* Create a new FB summary and report workbook for this snapshot of Facebook data */ filename fbout "c:\temp\FBReport_&SYSDATE..xlsx"; /* A little ODS style trick to make headings in my sheet */ ods escapechar='~'; %let bold_style=~S={font_size=12pt font_weight=bold}~; /* CREATES a new XLSX file */ ods excel (id=fb) file=fbout /* choose a style you like */ style=pearl /* SHEET_INTERVAL of NONE means that each PROC won't generate a new sheet automatically */ options (sheet_interval="none" sheet_name="Summary") ; ods noproctitle; ods text="&SYSDATE. Friend Report for &myFacebookName"; proc sql; select count(distinct(UserId)) as Number_Of_Friends into: NumberOfFriends from friends; quit; ods text="&bold_style.Count of friends by gender"; proc freq data=frienddetails order=internal; tables gender / nocum scores=table; run; ods text="&bold_style.Calculated Ages based on Graduation years"; proc means data=ages min max mean median p99; var age; class how; run; ods graphics on / width=800 height=300; ods text="&bold_style.Count of friends by Relationship Status"; proc freq data=frienddetails order=internal; tables relationshipstatus / nocum scores=table plots(only)=freqplot; run; ods excel (id=fb) close; /* ADDS new SHEETS to the existing XLSX file */ proc export data=frienddetails dbms=xlsx outfile=fbout replace; sheet="Friend Details"; run; proc export data=schoolfriends dbms=xlsx outfile=fbout replace; sheet="Schools"; run; proc export data=statusprep(keep=name date message) dbms=xlsx outfile=fbout replace; sheet="Latest Status"; run; |
I could achieve a similar result using other methods, of course. But I like to take advantage of my consulting opportunities to explore new features in SAS software. I find that if I can learn a new feature "on the job", I can produce a good result for my customer while also adding to my bag of SAS tricks.
Related articles
Experimenting with ODS EXCEL to create spreadsheets from SAS
How do I export from SAS to Excel files: Let me count the ways
Running PROCs on Your Facebook Friends (2011 version)
40 Comments
Chris,
I just hope SAS would include the EXCEL reading and writing component of the SAS/ACCESS INTERFACE TO PC FILES as part of BASE SAS in the near Future and NOT require one to buy a separate License to accomplish this. Excel is common source of data these days and reading EXCEL files on the Unix System is made Possible effectively using SAS ACCESS FILES INTERFACE TO PC FILES but with added COST. My company especially is NOT willing to invest in this additional software just to read the Excel files on Unix Environment.
Prashant, thanks for the comment. I think you've been following the tech advancements in SAS around dealing with Excel files, and you can see things have improved quite a bit in the past couple of releases. Aside from ODS EXCEL (for writing files), we now have XLSX export/import support on Unix without the need for a separate PC Files Server node -- but it still requires that SAS/ACCESS to PC Files license. I can't say what the future holds here...whether that will change anytime soon. But I'd suggest that it's a good conversation to have with your SAS site rep about the business needs and making sure that you have the correct mix of SAS products to support the work you're doing.
Unbelievable that his request was written in 2014 and seven years later, it is still requiring a separate licence to work at a basic level with such a universally used tool such as Excel.
I hear you. For those interested, in the SAS Viya world (v2020 and later), the SAS/ACCESS functions are included and don't require a separate license. Not only for Excel but for databases as well, so you have more flexibility in your data access options.
Pingback: Why your DDE programs don't work anymore - The SAS Dummy
Chris,
I am trying to use the Excel tagset but it does not appear to be installed in my system (9.4 M2) and I cannot find it on the SAS website. How does one get this?
Dwight, it's not a tagset, but an "official" ODS destination. Try this program:
At work, I have used ODS Excel create an excel file. The Style is Pearl. I need to send another data set to the same excel file. If I use ODS EXCEL, the previous file will be replaced by the current one. That's not what I want. If I use Proc Export, dbms=xlsx to send the data set to the same file in a new tab, the format of the new tab is different. I also get an error message when I open the file "Excel found unreadable content in "...". Can I use ODS Excel send multiple datasets in one file at the different time? Thanks!
You can create multiple sheets in one job by adding statements in-between each desired sheet like:
ods excel options(sheet_interval='none' sheet_name='Next_Sheet_Name');
Or you can try LIBNAME XLSX to create multiple sheets of data sets as described in this blog post.
Chris,
I am trying to run the below code on SAS 9.4M3 .I am only using GRIDLINES option in the ODS EXCEL Statement. But it does not work as i do not see any gridlines in the output file ie temp.xlsx. So what is the purpose of this Option?
ods excel file="~/temp.xlsx"
options(gridlines="yes"
);
proc print data=sashelp.class;
run;
ods excel close;
Thanks.
I think GRIDLINES is partly the ODS option, and partly the style. Here's an example adapted from a SAS Note:
ods path reset; ods path(prepend) work.styles(update); proc template; define style styles.test; parent=styles.minimal; style body from body / background=white; end; run; ods excel file="c:\temp\gl.xlsx" style=styles.test; proc print data=sashelp.class; run; ods excel close;Hello,
Please I need to know if there is a way to export a proc tabulate to an existing excel file that has more than one sheet updating only the required with the proc tabulate results, is that doable on SAS.
thanks
To add to an existing workbook, you'll have to use PROC EXPORT to add/replace a sheet. You can use TABULATE to create the output data set (see this note), then PROC EXPORT to send it to Excel.
Thanks for you response but
what I am asking about that I have more than one proc tabulate that I am exporting for one HML and I have an excel template file that has more than one sheet and want to update a specific sheet with the raw file from the sas
Thanks
There are a couple of ways to update an existing Excel file from SAS:
- Use PROC EXPORT to replace an entire sheet in an existing workbook
- Use the EXCEL libname (or PCFILES libname from 64-bit SAS or Unix) and then DATA step or SQL to update a sheet in-place, the same as you would a database table
- Use DDE (but that requires SAS and Excel on the same machine -- very specific configuration that isn't as common as it once was).
Hi,
I learned a lot from your example. However, because I was also using ODS for a data with a lot of rows, I think my ODS Exel bogged down resulting in a 0kb Excel file. My output in Excel is formatted so I'm unable to use PROC EXPORT. Is there a way to work around this issue? Thanks.
Make sure you're running with the latest version of SAS with hotfixes. If the problem still occurs, I recommend working with SAS Tech Support to identify the cause.
An alternative approach: create an Excel "template" file that contains formatting rules and sheet references on the "report" sheet, and use PROC EXPORT to populate another "hidden" sheet with the detail data that would be displayed in the formatted report. That's more complicated to set up, and it might not be workable for your particular report. But there is always more than one way to "skin the Excel cat".
Hi Chris,
I always use MS excel to send analysis results to my clients, and I often produce results of statistical analyses that require a few different procedures for a model, for ex. a proc means, a proc freq, and then a proc glm (the variables in the glm model are shown first in the means and freq output). I may produce several analyses for different models, and package the whole output into a spreadsheet with 1 worksheet per model, each worksheet containing output from 3 procedures.
Up until now I've done this by using ods tagsets.msoffice2k to create multiple single-worksheet "spreadsheet" files (that are really html files). I then use an ods tagsets.msoffice2k_x statement with the worksheet_source option set equal to a list of those same "spreadsheet" files, which creates an html file containing multiple worksheets. Each of these worksheets contains the content of a single single-worksheet "spreadsheet" files. Ack, I hope you understand what I just wrote; it's complex! But it works. It's also annoying because I have to open the output, tell excel that yes I'm sure I want to open this wierd (sorry html!) file, and then do a "save as" to convert the faux (html) spreadsheet into a real spreadsheet.
So when I saw your notes on the new ODS Excel statement, I thought: finally, a way to simplify this kludgy, complicated mess. So I want to use ods excel to create multi-worksheet spreadsheets where each worksheet contains output from multiple analytic procedures. Is there currently a way to do this? If not, is there any hope that it will be available in the future?
many thanks!
Yes - you can use the SHEET_INTERVAL option to control when to "break" the content across different sheets. The default behavior is "TABLE" -- each table appears on its own sheet. But you can use SHEET_INTERVAL="none" to keep multiple steps on a sheet, then use SHEET_INTERVAL to change/break when needed. Valid values are:
(SHEET_INTERVAL= 'BYGROUP' | 'PAGE' | 'PROC' | 'NONE' | 'TABLE')
Hi Chris,
I am working with the ODS EXCEL destination. I am using the SHEET_INTERVAL='none' option and SHEET_NAME= option to manually define the when to write to a new sheet. I am finding that my code is running without error however the information outputed to the second defined sheet is not actually writing out to EXCEL. Have you experienced this with the ODS EXCEL Destination?
Here is a snippet of my code.
ods excel file="&rootin.&delim.Outputs&delim.&outname..xlsx" style=listingSF DPI=300 options( embedded_titles='Yes' embedded_footnotes='Yes' SHEET_INTERVAL='None' ); %if &atyp=DESCRIPTIVE %then %do; ODS EXCEL options (sheet_name="Descriptive Statistics"); /*This section sets creates the text for the report title. */ title j=left lspace=0 height=11pt font="&font." bold "Table 7.2.1 Baseline Demographics, Full Dataset"; /*place holder for the report footnote */ footnote j=left font="&font." height=11pt "Source: &subjectdata"; proc report data=descrptshell (where=(subgroups NE "OVERALL")); /* removed additional lines to save space>*/ run; title; footnote; ODS EXCEL options (sheet_name="Additional Information"); %if &OthRaceCnt. >0 %then %do; proc report data=otherrace; column &race.xxx count; define &race.xxx / "Other Races"; define count / "N"; run; %end; %if &OthRegCnt. >0 %then %do; proc report data=otherregion; column ccatxxx count; define ccatxxx / "Other Countries"; define count / "N"; run; %end; %end;The proc report is after sheet_name="Descriptive Statistics" is writing out. However the 1st proc report after the line "ODS EXCEL options (sheet_name="Additional Information");" is not writing out to the excel file. the 2nd proc report shouldn't print with my test data because the if condition is not true.
The log indicates that the proc report runs without error. Below is a snippet from the log at this point.
MPRINT(CREATEREPORT): title; MPRINT(CREATEREPORT): footnote; MPRINT(CREATEREPORT): ODS EXCEL options (sheet_interval='None' sheet_name="Additional Information"); MPRINT(CREATEREPORT): proc sql; MPRINT(CREATEREPORT): select count(*) into:OthRaceCnt from otherrace; MPRINT(CREATEREPORT): select count(*) into:OthRegCnt from otherregion; MPRINT(CREATEREPORT): quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.02 seconds cpu time 0.01 seconds SYMBOLGEN: Macro variable OTHRACECNT resolves to 1 MLOGIC(CREATEREPORT): %IF condition &OthRaceCnt. >0 is TRUE MPRINT(CREATEREPORT): proc report data=otherrace; SYMBOLGEN: Macro variable RACE resolves to race MPRINT(CREATEREPORT): column racexxx count; SYMBOLGEN: Macro variable RACE resolves to race MPRINT(CREATEREPORT): define racexxx / "Other Races"; MPRINT(CREATEREPORT): define count / "N"; MPRINT(CREATEREPORT): run; NOTE: There were 1 observations read from the data set WORK.OTHERRACE. NOTE: PROCEDURE REPORT used (Total process time): real time 0.02 seconds cpu time 0.03 seconds SYMBOLGEN: Macro variable OTHREGCNT resolves to 0 MLOGIC(CREATEREPORT): %IF condition &OthRegCnt. >0 is FALSE MPRINT(CREATEREPORT): ods excel close; NOTE: Writing EXCEL file: C:\Users\sakatz\Documents\Projects\FDA\DSAP_v2_2\Outputs\Test_SK.xlsx MPRINT(CREATEREPORT): ods listing;Any thoughts as to why the 2nd worksheet is not being created? am I missing an option?
Thank you,
Sara
Good question! If you can simplify to a standalone program, I recommend posting to the SAS Support Communities and see what the experts say. I've also sent to an internal expert and if I get an answer, I'll post back.
Chevell Parker share the answer with me. You need to create a piece of "dummy" output with SHEET_INTERVAL='OUTPUT', then resume with SHEET_INTERVAL='NONE'. Here's the SAS Note with sample code.
I arrive at this solution on my own--combining ods excel for a summary proc tabulate with proc exports for several record-level detail tabs. I was generally pleased, but for one issue: formatting of the proc export tabs!
Those tabs inherited the formatting form the first tab (created in ODS and, I presume, rendered with the default Excel ODS style), but it did so in some bizarre way that made all numeric columns look like table headings (bold, shaded backgrounds and left-aligned).
I spent a day beating my head against the wall trying to fix it but ended up just doing detail tabs with ods excel and proc print. It seems horribly inefficient and stupid-slow compared to proc export but I could find no other way to make it look decent.
Any ideas for how to fix?
Try using the ODS EXCEL statement between your output steps to change the style. Here's an example.
ods excel file="c:\temp\temp.xlsx" options(sheet_interval="none" sheet_label="Fancy") style=Dove; proc print data=sashelp.class(where=(sex="M")); run; proc means data=sashelp.class(where=(sex="M")); run; /* Create a dummy worksheet using the DATA _NULL_ step */ ods excel options (sheet_interval="output" sheet_label="Plain" ) style=Excel; ods exclude all; data _null_; declare odsout obj(); run; ods select all; proc print data=sashelp.cars(where=(origin="USA")) noobs; run; ods excel close;Hi Chris,
I'm running into issues keeping the ODS text style in the 2nd sheet I create. My text/header that is supposed to start the new sheet remains on the first sheet. For example (in code below), my new sheet should start with the text "Sheet 2 Table 1" (in the style "my_style" I created upfront). Instead, this is ending up as the last line on my first sheet. Any ideas as to why? Thanks!!
CODE:
filename sumstat ".xlsx"; ods escapechar='~'; %let my_style=~S={font_size=11pt font_weight=bold}~; /********************************************************************* Sheet 1 *********************************************************************/ ods excel (id=stk) file=sumstat style=plateau options (sheet_interval="none" sheet_name="sheet1") ; ods noproctitle; ods text="&my_style.Sheet 1 Table 1; proc freq data = indata; table q1 / list missing nocum; format q1 stk_type.; run; ods text="&my_style.Sheet 1 Tables 2&3"; proc freq data = indata; where q1 = 1; table q2 q3 / list missing nocum; format q1 stk_type. q2 years. q3 biosim_work. ; run; /**/ /************* Dummy ODS *************/ ods exclude all; data _null_; declare odsout obj() ; run; ods select all; ods escapechar='~'; %let my_style=~S={font_size=11pt font_weight=bold}~; /********************************************************************* Sheet 2 *********************************************************************/ /* New sheet */ ods excel (id=stk) style=plateau options (sheet_interval="none" sheet_name="sheet 2); ods noproctitle; ods text="&my_style.Sheet 2 Table 1"; proc freq data = indata order = freq; table q13_1/ list missing nocum; run; ods excel (id = stk) close;Try adding this line before your "dummy ODS" DATA step:
ods excel (id=stk) options(sheet_interval="output");Hi, Chris
I created a .xlsx file with a graph (graph1) in worksheet1. (same as the example code in your post).
Now I want to add another worksheet(sheet2) with a new graph(graph2) in the same xlsx file.
What is the best way to do it? (Can't do it by DDE as permission issue in SAS sever).
Thanks
Steve,
You can accomplish this with the ODS EXCEL OPTIONS SHEET_NAME and SHEET_INTERVAL options. See this communities discussion thread for some hints.
Hi
I am using ods excel to create a spreadsheet with multiple tabs. The file is emailed to multiple users every morning. However when you open the file all the tabs are selected. Thus if you select cell A1 in the first tab it selects cell A1 in all the tabs. Now of course I know to deselect all the tabs and then select the tab I want to work in. However not all users know that. What can I do so that only one tab is selected when I open it rather than all tabs grouped (assuming this is an ods quirk)?
Thank you.
I have the same problem!!
Hi Chris,
I'm trying to generate a excel report with multiple tabs (Sheets). For eg. Age, in one sheet, Gender table in the second sheet, race in the third tab. I have the titles for each table. When I have used ODS excel the first title is correctly displayed (e.g. Infectious Diseases by Age) but the second title for Gender (infectious diseases by Gender) is displayed at the end of the table in the first sheet. It is not displaying on the correct sheet or tab in excel for Gender and if I have a third tab RACE the title for Race is displayed on the second sheet under the Gender Table. I'm using the following code:
ods excel options(sheet_interval='none' sheet_name='Age');
ods text="&bold_style. Infectious diseases by Age";
proc freq data=age_cat;
tables Patient_Age;
run;
/*++++++++++++++ Table= Infectious diseases by Gender++++++++++++++++++++*/
ods excel options(sheet_interval='none' sheet_name='Gender');
ods text="&bold_style. Infectious diseases by Gender";
proc freq data=age_cat;
tables Patient_Gender;
run;
ods excel close;
You might need to apply the SHEET_INTERVAL trick from this SAS Note.
Hello Chris,
Great post and great blog. I learned a couple of nice things from this post. One thing I am having trouble with is if while in "ods-excel-mode" I create a new sheet, and then try to put text on the new sheet using ods text, the text will actually print to the bottom of the previous sheet. i.e. it seems like the new sheet isn't being created until I use a proc tabulate:
sample code:
%macro newsheet; ods excel options(sheet_interval='output'); ods exclude all; data _null_; declare odsout obj(); run; ods select all; %mend newsheet; /* end of macros */ ods excel file='/sasdata/path/path/my spreadsheet.xlsx' options (sheet_interval='none' sheet_name='first sheet); proc tabulate data= ......... /*imagine proc tabulate code here*/ run; %newsheet; /* macro for instancing a new sheet*/ ods excel options (sheet_interval='output' sheet_name='second sheet'); ods text="I want this to print at the top of the second sheet but it prints at the bottom of the second sheet"; proc tabulate data= ......... /*imagine proc tabulate code here*/ run;See this discussion on the SAS Support Communities. Consider using TITLE/FOOTNOTE and the embedded_titles option.
Hi Chris,
Great information! I’m relatively new to SAS EG. Would you by any chance know if I can combine ods excel (code below) with either another proc report added to the ods process or proc export in order to create a report that breaks data by each excel sheet depending on a value of a particular field called SHEETNM (#byval5) and then add to each of those sheets additional data.
Code below does the first task of created 1st section of the report
- Titles 3 & 4 are different depending on the sheet
I just don’t know how I can append additional sections to each of those sheets.
ODS EXCEL FILE=&OUTFILE01.
OPTIONS(EMBEDDED_TITLES='YES'
EMBED_TITLES_ONCE= 'YES'
EMBEDDED_FOOTNOTES = 'YES'
START_AT='1,1'
GRIDLINES='ON'
ORIENTATION = 'LANDSCAPE'
FROZEN_HEADERS='YES'
FROZEN_ROWHEADERS = 'NO'
TITLE_FOOTNOTE_NOBREAK='YES'
SHEET_NAME = '#BYVAL5'
SHEET_INTERVAL='BYGROUP'
SHEET_LABEL = '#BYVAL5'
);
TITLE j=l 'ABC Company';
TITLE2 j=l 'Time Period: ' &BEGIN_DT. ' - ' &END_DT.;
TITLE3 J=L '#BYVAL2 #BYVAL3';
TITLE4 J=L '#BYVAL4';
TITLE5 J=LEFT ' ';
TITLE6 J=L 'Paid Claims Per Participant Per Month by Service';
PROC REPORT DATA=WORK.RPT1 NOWD HEADLINE HEADSKIP SPLIT= '*';![$#,##0.00]()
' CELLWIDTH=1.00in};![$#,##0.00]()
' CELLWIDTH=1.00in};![#,##0.00]()
' CELLWIDTH=0.25in};
BY KEYCOL GROUP_NAME STATUS REPORTING_STATUS SHEETNM;
COLUMN KEYCOL REC CATEGORY TIMEFRAME,(' ' MEASURE PPPM DUMMY);
DEFINE KEYCOL / GROUP PAGE NOPRINT;
DEFINE REC / GROUP NOPRINT;
DEFINE CATEGORY / GROUP ' ' LEFT
STYLE(COLUMN)={JUST=L TAGATTR='WRAPTEXT:NO' WIDTH=100% CELLWIDTH=1.50in};
DEFINE TIMEFRAME / ACROSS ' ' ORDER=DATA LEFT
STYLE(COLUMN)={JUST=L TAGATTR='WRAPTEXT:NO' WIDTH=100%};
DEFINE MEASURE / ANALYSIS 'Dollars'
STYLE(COLUMN)={JUST=R TAGATTR='FORMAT:$#,##0.00_);[RED]
DEFINE PPPM / ANALYSIS 'PPPM'
STYLE(COLUMN)={JUST=R TAGATTR='FORMAT:$#,##0.00_);[RED]
DEFINE DUMMY / COMPUTED NOZERO ' '
STYLE(COLUMN)={JUST=R COLOR=WHITE TAGATTR='FORMAT:#,##0.00_);[RED]
RBREAK AFTER / SUMMARIZE;
COMPUTE DUMMY;
DUMMY = '';
ENDCOMP;
COMPUTE AFTER KEYCOL;
LINE ' ';
ENDCOMP;
COMPUTE AFTER;
CATEGORY="Total";
LINE ' ';
ENDCOMP;
RUN;
ODS EXCEL CLOSE;
If I add another proc report to existing code, additional excel sheets are generated (called as the ones above with number ‘2’ added to the name of the sheet:
PROC REPORT DATA=WORK.RPT2 NOWD NOHEADER PLIT= '*';![#,##0.00]()
' CELLWIDTH=1.00in};![#,##0.00]()
' CELLWIDTH=1.00in};![#,##0.00]()
' CELLWIDTH=0.25in};
BY KEYCOL GROUP_NAME STATUS REPORTING_STATUS SHEETNM;
COLUMN KEYCOL REC CATEGORY TIMEFRAME,(' ' DUMMY1 MEASURE DUMMY2);
DEFINE KEYCOL / GROUP PAGE NOPRINT;
DEFINE REC / GROUP NOPRINT;
DEFINE CATEGORY / GROUP ' ' LEFT
STYLE(COLUMN)={JUST=L TAGATTR='WRAPTEXT:NO' WIDTH=100% CELLWIDTH=1.50in};
DEFINE TIMEFRAME / ACROSS ' ' ORDER=DATA LEFT
STYLE(COLUMN)={JUST=L TAGATTR='WRAPTEXT:NO' WIDTH=100%};
DEFINE DUMMY1 / COMPUTED NOZERO ' '
STYLE(COLUMN)={JUST=R COLOR=WHITE TAGATTR='FORMAT:#,##0.00_);[RED]
DEFINE MEASURE / ANALYSIS ' '
STYLE(COLUMN)={JUST=R TAGATTR='FORMAT:#,##0.00_);[RED]
DEFINE DUMMY2 / COMPUTED NOZERO ' '
STYLE(COLUMN)={JUST=R COLOR=WHITE TAGATTR='FORMAT:#,##0.00_);[RED]
COMPUTE DUMMY1;
DUMMY1 = '';
ENDCOMP;
COMPUTE DUMMY2;
DUMMY2 = '';
ENDCOMP;
COMPUTE AFTER KEYCOL;
LINE ' ';
ENDCOMP;
RUN;
Could I use proc export instead and in the sheet name specify ‘#byval5' aka SHEETNM do direct additional data to right sheets of the existing report? If so, how can I accomplish that?
Renata, this is a great question to post into the SAS Support Communities. I see that you're a member! There are some deep experts in ODS EXCEL there -- try the ODS and Base Reporting board.
Quickly though...
You can change the ODS EXCEL options "mid stream". Best practice is to assign an ID to your initial ODS EXCEL destination:
ODS EXCEL (ID=myreport) FILE=&OUTFILE01. OPTIONS(EMBEDDED_TITLES='YES' ...Then if you want to change the SHEET_INTERVAL behavior in-between PROC steps, add a statement like:
This keeps the current Excel destination open but changes the output behavior for subsequent steps. Beyond this, if you have the detail data that you want to simply add to the workbook after ODS EXCEL CLOSE, then you can use PROC EXPORT -- one step per set of data that you want to add. If it's a single data source with BY groups that you want to break up, I think you'll have to iterate and have a PROC EXPORT for each BY value. Might need to use a macro loop to iterate through them.
Hi, Why aren't we getting border around exported data using PROC EXPORT when it is used after ODS EXCEL statement.Like for instance in your Friends Details - Detailed tab created using PROC EXPORT there is not border around the data. Is there is a way to solve it ?
PROC EXPORT sends just data, not formatted report output. ODS EXCEL allows you to add more formatting/style options to control the look of your Excel content.
Hi Chris,
Could I ask a question about ODS excel? I have a dataset with 44,000 records and I would like to send it to excel using ODS excel statement. But it meet a error indicates 'ERROR: Fatal ODS error has occurred, Unable to continue processing this output destination.'. And when I delete several records to about 40,000, it will succeed. So do you know the reason or it is due to ODS excel couldn't deal with so large data? Thanks a lot.
Since ODS Excel includes report format information, the data sent to Excel can be larger than if it's just a straight PROC EXPORT. Try applying a minimal ODS style, and eliminate any columns that you don't need from the data as well.