BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Q1983
Lapis Lazuli | Level 10

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

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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

4 REPLIES 4
ballardw
Super User

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.

Q1983
Lapis Lazuli | Level 10

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
data_null__
Jade | Level 19

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;

Q1983
Lapis Lazuli | Level 10

That worked.  I had not used DEFINE _ALL_ previously  Thanks

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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