Desktop productivity for business analysts and programmers

Getting all sub totals of numeric and date fields

Frequent Contributor
Posts: 97

Getting all sub totals of numeric and date fields


I am using this piece of code

I want sub totals on Quantity Received, Net Goods Value and Purchase Cost only but it is showing the sub totals for date and all numeric columns

Also I want to reduce the width of the fields Quantity Received, Net Goods Value and Purchase Cost like the same I have done for 'Order Type' using flow width. The numeric field gets left aligned

Also how can I avoid getting a pagebreak after the group. The page should break only after the pagse size specified

options nobyline;
proc report data=WORK.QUERY3051 nowd headskip
by Pi_In_Supplier Cf_Su_Name;
title j=l 'SUPPLIER : #byval(Pi_In_Supplier) #byval(Cf_Su_Name)';

column Pi_In_Supplier Cf_Su_Name Gb_Br_Name
Pi_In_Grnno Pi_In_Grn Pi_In_Orderno Pi_In_Ordtype Pi_In_Partno
Pi_In_Grndate Pi_In_Recqty Pi_In_Recgoods Pi_In_Recprice;
define Pi_In_Supplier / group noprint ;
define Cf_Su_Name / group noprint ;
define Gb_Br_Name / 'Branch' flow width=20;
define Pi_In_Grnno / 'Internal GRN No';
define Pi_In_Grn / 'Supplier GRN No';
define Pi_In_Orderno / 'Order No' ;
define Pi_In_Ordtype / 'Order Type' flow width=5;
define Pi_In_Partno / 'Product Code' flow width=10;
define Pi_In_Grndate / 'Date Goods Received';
define Pi_In_Recqty / 'Quantity Received' ;
define Pi_In_Recgoods / 'Net Goods Value' ;
define Pi_In_Recprice / 'Net Purchase Cost' ;
break after Pi_In_Supplier /summarize skip ul ol;

compute after Pi_In_Supplier;
Pi_In_Supplier = 'SubTot of'||trim(Cf_Su_Name);
Posts: 9,433

Re: Getting all sub totals of numeric and date fields

In a define statement for a numeric variable, the default usage is "analysis" with a default statistic of "sum". Therefore, all of these DEFINE statements are the same:
define sales/ 'Sales' ;
define sales/ sum 'Sales' ;
define sales/ analysis sum 'Sales' ;
define sales/ analysis 'Sales' ;
...and I would get a sum on the Sales column even if I had the first DEFINE statement.

So if your date variable does NOT have a usage, then you are telling SAS to use the SUM statistic for the date variable. Try explicitly putting a usage of DISPLAY for the date variable (in which case, it won't be summed up) or a usage of ORDER or GROUP:
define Pi_In_Grndate / display 'Date Goods Received';
define Pi_In_Grndate / order 'Date Goods Received';

Remember that a usage of order will maintain the report as a detail report. A usage of group would "collapse" all the rows into a summarized report -- with the same date into one row (depending on the usage of the other variables in the COLUMN statement).
Ask a Question
Discussion stats
  • 1 reply
  • 2 in conversation