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
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;
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.
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.12 | 0.1653497 | 0.3020011 | 1.3027911 | 6.7465918 | 2.4742629 | 7.5079125 | 2.9190397 | 4611.4934 | 4575.5461 |
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;
That worked. I had not used DEFINE _ALL_ previously Thanks
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.