The SAS Output Delivery System and reporting techniques

Very interesting ODS output issue

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 135
Accepted Solution

Very interesting ODS output issue

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


Accepted Solutions
Solution
‎09-30-2014 12:11 PM
Respected Advisor
Posts: 3,786

Re: Very interesting ODS output issue

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;

View solution in original post


All Replies
Super User
Posts: 11,105

Re: Very interesting ODS output issue

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.

Frequent Contributor
Posts: 135

Re: Very interesting ODS output issue

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          6         12340      2575583638.120.16534970.30200111.30279116.74659182.47426297.50791252.91903974611.49344575.5461
Solution
‎09-30-2014 12:11 PM
Respected Advisor
Posts: 3,786

Re: Very interesting ODS output issue

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;

Frequent Contributor
Posts: 135

Re: Very interesting ODS output issue

That worked.  I had not used DEFINE _ALL_ previously  Thanks

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 494 views
  • 3 likes
  • 3 in conversation