topic Re: How to report in excel well in SAS Procedures

How to report in excel well

zhangda — Fri, 14 Oct 2016 14:18:34 GMT

Hello!

I am coding to report in excel workbook attachment, which includes two sheet tabs, one is 2015 product, another is 2016 product. For first sheet tab, I have 2015 products, including four product , A, B, C, D, but why the last product D showing in the first part of the second sheet tab, which is supposed to be 2016 product only. Would you please help me out? The code below is incomplete, just an example, because I am thinking there must be an option that could solve the issue, the attachment is my result. Thanks!

ODS LISTING CLOSE;

ODS TAGSETS.EXCELXP file="/opt/sas/sasmain/Project.xls"

STYLE=minimal options( Autofit_height='yes'

frozen_rowheaders='yes' sheet_interval='none' embedded_footnotes='yes'

sheet_label = ' '

embedded_titles = 'yes'

suppress_bylines = 'yes' sheet_name='2015 Product

/* autofilter='all' autofilter_table='2'*/

Orientation = 'landscape' width_fudge='0.1'

);

ods tagsets.ExcelXP options(sheet_interval='none' sheet_name=2015 Product ');

Proc report data=allproduct_dqm contents=" " nowd split='\'

/*style(report)={font_size=14pt cellpadding=4pt cellspacing=1 rules=none frame=void}*/

style(header)={color=white background=cornflowerblue height=1.3in fontsize=1.5 }

style(column)={just=center background=snow foreground=black cellwidth=1in foreground=black fontsize=1.5};

title1 j=c color=black HEIGHT=10pt BOLD "Credit Metrics for All Products";

column _name_ year2016 year2015 Num_of_Variance mature2015 Num_of_Variance2 y2016 y2015 Variance ;

define _name_ / "Metrics" style=[foreground=negfmt.];

define year2016/ "&Apr_Yr." style=[foreground=negfmt.];

compute _name_ ;

if _name_= 'Avg FICO' then call define(_row_,'style','style={background=gainsboro}');

endcomp;

%macro comp(arg);

compute &arg;

If _name_='2015 Product' then call define(_col_,'format','comma20.0');

If _name_='CreditAR' then call

endcomp;

%mend common_lines;

ods tagsets.ExcelXP options(sheet_interval='none' sheet_name='2016 Product');

Proc report data=allproduct_orig contents=" " nowd split='\'

style(header)={color=white just=center background=lightgrey fontweight=bold }

style(column)={color=white just=center background=lightcyan foreground=black };

column _name_ month2016 month2015 Num_of_Variance Perc_of_Variance year2016 year2015 Perc_of_Variance_y;

title1 j=c color=black HEIGHT=10pt BOLD " All Products";

define _name_ / "Originations" style=[fontstyle=Roman fontweight=bold width=2in font_size=2.2 cellwidth=2in just=center foreground=negfmt.];

Re: How to report in excel well

ChrisHemedinger — Fri, 14 Oct 2016 14:21:17 GMT

Others might have input on your format and approach, but my first advice is this. If you have SAS 9.4m3, use ODS EXCEL instead of ODS TAGSETS.EXCELXP. The syntax is nearly identical, and you'll get a native xlsx file that's much smaller in size and more acceptable to Microsoft Excel.

Re: How to report in excel well

RW9 — Fri, 14 Oct 2016 14:29:36 GMT

Sorry, its not going to be possible to work out any issue from that. The code is mostly missing, and I wouldn't download any Excel files due to security risks. Post some example test data - just a couple of lines, in a datastep, and the full export code so that we can run it. If a variable is really appearing on another sheet then I imagine the code - with the macro part - is not resolving correctly - however I can't see your log so can't tell you. If its just appearing on another page, you have too many variables for one page.

One other tip, post code in code window - {i} or the SAS run symbol above the post area. Remove commented blocks, use consistent indetation and capitilisation to make code as readbel as possible,

Sorry, looking at it further there are nnumerous errors/typos in that code, here I updated a bit for you. There are no product A B C D variables given anywhere, are these observations if so then that is what is in that dataset:

ods listing close;
ods tagsets.excelxp file="/opt/sas/sasmain/Project.xls"
  style=minimal options( Autofit_height='yes'
        frozen_rowheaders='yes' sheet_interval='none' embedded_footnotes='yes'
        sheet_label = ' '
        embedded_titles = 'yes'
        suppress_bylines = 'yes' sheet_name='2015 Product' Orientation = 'landscape' width_fudge='0.1');
ods tagsets.ExcelXP options(sheet_interval='none' sheet_name='2015 Product ');
 
Proc report data=allproduct_dqm contents="   " nowd split='\'
  style(header)={color=white background=cornflowerblue  height=1.3in fontsize=1.5 }
  style(column)={just=center background=snow foreground=black cellwidth=1in   foreground=black  fontsize=1.5};
  title1 j=c  color=black HEIGHT=10pt  BOLD "Credit Metrics for All Products";
  column _name_   year2016  year2015   Num_of_Variance mature2015  Num_of_Variance2 y2016 y2015 Variance ;  /* <- this shows the columns to output */

Example of typo: ods tagsets.ExcelXP options(sheet_interval='none' sheet_name=2015 Product ');

Missing quote before 2015.

Re: How to report in excel well

ballardw — Fri, 14 Oct 2016 14:41:23 GMT

When you use

(sheet_interval='none'

it means just that, all the data goes to the same sheet until a different interval is specified in an ODS tagsets.excelxp statement.

You may want (sheet_interval='proc'

to send all of the output to one sheet from a single Procedure call or if you have a procedure that generates multiple tables then 'table'

But since you have columns with names like year2016 year2015 y2016 y2015 in a single proc report definition it appears that you really do not want to separate the years.

If you have a variable to split the report on then use the BY statement in the proc and the table option for the sheet_interval. Maybe.

Re: How to report in excel well

zhangda — Fri, 14 Oct 2016 18:25:20 GMT

But I have 12 tables generated by proc report procedures. I want to display table 1-6 in the sheettab1, and 7-12 in sheettab2, they are not divided by group, thanks!

Re: How to report in excel well

ballardw — Fri, 14 Oct 2016 19:39:03 GMT

Your easiest solution may be to add a variable to group them by and use Sheet_Interval = 'Bygroup'. Which would put the output for separate values of the BY variable on separate sheets.

Re: How to report in excel well

RW9 — Mon, 17 Oct 2016 08:27:24 GMT

Its simply a matter of putting your proc reports in the right place:

ods tagsets.excelxp file=... options(sheet_name="First sheet" ...);
proc report data=table1...
proc report data=table2...
...

ods tagsets.excelxp options(sheet_name="Second sheet"...);
proc report data=table7...
...

ods tagsets.excelxp close;