topic Very interesting ODS output issue in ODS and Base Reporting

Very interesting ODS output issue

Q1983 — Tue, 30 Sep 2014 15:15:09 GMT

proc summary data = Actives_Curr_Yr_Pl_5_Yrs;

class Rate_Chg_Yr;

output out = Summary_Yr_Total

sum(ln_1st_prin_ba) = sum_upb

mean(ln_ann_int_rt) = avg_curr_ir

mean(max_rate2) = avg_max_ir

mean(Pmt_plus_0_0_Rem_Term_pct_chg2) = avg_est_pmt_chg2

mean(DTI) = avg_pre_dti

mean(DTI_LE_2) = avg_pre_dti_le_2

mean(Post_DTI2) = avg_post_dti

mean(Post_DTI2_LE_2) = avg_post_dti_le_2

mean(FICO_Rescore) = avg_FICO_Rescore

mean(FICO_Rescore_350_850) = avg_FICO_Rescore_350_850

;

run;

This produces about 7 lines of detail data with the above named headers

Here is the ODS code

ODS TAGSETS.ExcelXP

options(sheet_interval='Table' sheet_name='Yr Total' center_horizontal="yes"

fittopage="No" blackandwhite="Yes" Embedded_Footnotes='Yes' Embedded_Titles='Yes'

Frozen_Headers='3' absolute_column_width='8' autofit_height='Yes' orientation='landscape');

%NoAccountLogic(Summary_Yr_Total,&ReportName);

PROC REPORT DATA= Summary_Yr_Total headskip split='*' wrap nowd

;

COLUMNS (_ALL_)

run;

When I run this portion of the code, instead of getting the detail line by line I get a summed up field that totals all lines and shows only one row of data. I have another ODS wth the exact same

proc summary data = Actives_Curr_Yr_Pl_5_Yrs;

which outputs to a different dataset. This one works just fine and provides detail row by row. I am using the exact same ODS code for both. I do not know what the problem is here

Re: Very interesting ODS output issue

ballardw — Tue, 30 Sep 2014 15:20:52 GMT

What do you get from Proc Print data=Summary_Yr_Total; ?

Also, show both sets of Proc summary code and tells us which one is working "correctly". There may be a difference you are overlooking because of familiarity.

Re: Very interesting ODS output issue

Q1983 — Tue, 30 Sep 2014 15:36:48 GMT

This is the one that works and provides detail

proc summary data = Actives_Curr_Yr_Pl_5_Yrs;

class Port_Type;

output out = Summary_Inv_DQ

sum(DPD_Cur) = DPD_Cur

sum(DPD_30) = DPD_30

sum(DPD_60) = DPD_60

sum(DPD_90) = DPD_90

sum(DPD_120Pl) = DPD_120Pl

sum(DPD_Miss) = DPD_Miss

;

run;

I think it may be because the other example uses Mean if that might make a difference. I should get detail data for both examples however I only get it for this example. When I run the other code I get something like this. I am just populating a few of the headers here. The point is I only get the summed totals of the entire dataset instead of the detail

rate_chg_yr type freq bal

12099

12340

2575583638.12

0.1653497

0.3020011

1.3027911

6.7465918

2.4742629

7.5079125

2.9190397

4611.4934

4575.5461

Re: Very interesting ODS output issue

data_null__ — Tue, 30 Sep 2014 16:11:23 GMT

You need DEFINE with DISPLAY option. When are variables are numeric you get SUM. Add LIST option to PROC REPORT statement to see how PROC REPORT interprets your statements.

DEFINE _ALL_ / display;

Re: Very interesting ODS output issue

Q1983 — Tue, 30 Sep 2014 16:18:58 GMT

That worked. I had not used DEFINE _ALL_ previously Thanks