Re: Proc Report Subtotal Labels

arjessup · Posted 05-23-2019 11:06 AM

I am trying to create a subtotal line label in a report that includes a variable value. My report lists accounts for a particular client, I would like to have the subtotal to read as Subtotal for John Doe. My current code is:

compute after client_guarantor_name_group;
client_guarantor_name=catt("Subtotal of ",CLIENT_GUARANTOR_NAME_GROUP);

My results are as follows:

Acct. #	Client	Net Balance
1	John Doe	2,500
2	John Doe	500
3	John Doe	3,000
	Subtotal of	6,000
18	Jane Smith	450
19	Jane Smith	950
21	Jane Smith	1,000
22	Jane Smith	1,100
	Subtotal of	3,500

I get the words Subtotal of, but not the client name. Is there a way to do this?

ballardw · Posted 05-23-2019 11:40 AM

Is your log showing anything like?:

WARNING: In a call to the CATT function, the buffer allocated for the result was not long enough
         to contain the concatenation of all the arguments. The correct result would contain 21
         characters, but the actual result might either be truncated to 8 character(s) or be
         completely blank, depending on the calling environment. The following note indicates the
         left-most argument that caused truncation.

Part of the issue could be that the defined length of your client_guarantor_name variable is too short to hold the resulting text.

You really need to provide the entire proc report code as orders of variables and options can interact. Also some example data in the form of a data step would allow us to actually test code with data that actually looks like yours.

Also any formats applied to variables can affect the result

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

This example modified from the documentation is similar to what you want. There are two sections to comment out that will then result in output with the undesired behavior on the summary line.

data grocery;
   /* comment out the following line to see different behavior*/
   length department $ 22;
   input Sector $ Manager $ Department $ Sales @@;
   datalines;
se 1 np1 50    se 1 p1 100   se 1 np2 120   se 1 p2 80
se 2 np1 40    se 2 p1 300   se 2 np2 220   se 2 p2 70
nw 3 np1 60    nw 3 p1 600   nw 3 np2 420   nw 3 p2 30
nw 4 np1 45    nw 4 p1 250   nw 4 np2 230   nw 4 p2 73
nw 9 np1 45    nw 9 p1 205   nw 9 np2 420   nw 9 p2 76
sw 5 np1 53    sw 5 p1 130   sw 5 np2 120   sw 5 p2 50
sw 6 np1 40    sw 6 p1 350   sw 6 np2 225   sw 6 p2 80
ne 7 np1 90    ne 7 p1 190   ne 7 np2 420   ne 7 p2 86
ne 8 np1 200   ne 8 p1 300   ne 8 np2 420   ne 8 p2 125
;
proc format library=work;
   value $sctrfmt 'se' = 'Southeast'
                  'ne' = 'Northeast'
                  'nw' = 'Northwest'
                  'sw' = 'Southwest';

   value $mgrfmt '1' = 'Smith'   '2' = 'Jones'
                 '3' = 'Reveiz'  '4' = 'Brown'
                 '5' = 'Taylor'  '6' = 'Adams'
                 '7' = 'Alomar'  '8' = 'Andrews'
                 '9' = 'Pelfrey';
   /* comment out the (default=22) to see different 
      behavior*/
   value $deptfmt (default=22) 
      'np1' = 'Paper'
      'np2' = 'Canned'
      'p1'  = 'Meat/Dairy'
      'p2'  = 'Produce';
run;

proc report data=grocery;
   title 'Sales for Individual Stores';
   column sector manager department sales ;
   define sector / group ;
   define manager / group noprint;
   define sales / analysis sum format=dollar11.2;
   define department / group format=$deptfmt. style=[cellwidth=2in];
   break after sector / summarize ;
   compute after sector;
      department=catx(' ','Summary for', put(sector, $sctrfmt.));
   endcomp;
run;

arjessup · Posted 05-23-2019 12:34 PM

Below is my code, although this code does give me a lable.

data WORK.TEST;
infile datalines;
input Client_Name_Group:$40. Client_Name:$40. Acct_Nbr:21. Net_Balance:comma32.2;
datalines;
John John 1 2,500.00
John John 2 5,000.00
John John 3 250.00
John John 4 1,500.00
John John 5 2,300.00
John John 6 200.00
John John 7 900.00
John John 8 3,200.00
Jane Jane 9 750.00
Jane Jane 10 1,300.00
Jane Jane 11 2,700.00
Jane Jane 12 500.00
Jane Jane 13 400.00
Jane Jane 14 2,900.00
Jane Jane 15 1,700.00
Jane Jane 16 2,500.00
Jane Jane 17 2,900.00
Jane Jane 18 2,400.00
Jane Jane 19 3,700.00
Jane Jane 20 3,600.00
;

ods escapechar="^";
options orientation=Portrait nodate nonumber;
ods excel file="\\pnfp.corp\isilon\DPTFolders\SystemsSupport\JackHenry Reports\Validated Downloads\E-mailed Report Output\TEST\test_LTOB..xlsx"
OPTIONS(absolute_column_width='50,50,50') ;
title 'Top 20 Relationships';
footnote justify=left "Date Printed: %sysfunc(today(),mmddyys10.)" justify=right "Page ^{thispage}";

proc report nowd data = test
style(summary)=[fontweight=bold backgroundcolor=aliceblue];
column Client_Name_Group Client_Name Acct_Nbr Net_Balance;
define Client_Name_Group / GROUP noprint;
define Client_Name / DISPLAY 'Client Name' style(column)={tagattr='wraptext:no' width=100%};
define Acct_Nbr / DISPLAY 'Acct. #' style(column)={tagattr='wraptext:no' width=100%};
define Net_Balance / ANALYSIS SUM 'Net Balance' style(column)={tagattr='wraptext:no' width=100%};

break after Client_Name_Group / ol skip summarize suppress;
rbreak after / summarize SKIP UL OL;
compute after Client_Name_Group;
client_guarantor_name=catt('Subtotal of ',Client_Name_Group);
endcomp;

run;

ballardw · Posted 05-23-2019 01:19 PM

This gets you closer:

proc report nowd data = work.test
style(summary)=[fontweight=bold backgroundcolor=aliceblue];
column Client_Name_Group Client_Name Acct_Nbr Net_Balance;
define Client_Name_Group / GROUP noprint;
define Client_Name / DISPLAY 'Client Name' style(column)={tagattr='wraptext:no' width=100%};
define Acct_Nbr / DISPLAY 'Acct. #' style(column)={tagattr='wraptext:no' width=100%};
define Net_Balance / ANALYSIS SUM 'Net Balance' style(column)={tagattr='wraptext:no' width=100%};

break after Client_Name_Group / ol skip summarize ;
rbreak after / summarize SKIP UL OL;
compute after Client_Name_Group;
Client_Name=catt('Subtotal of ',Client_Name_Group);
endcomp;

run;

Problems created by your current options: SUPPRESS on the Break for client_name_group means that value is not actually available.

Second you were creating a client_guarantor_name column but there is no such column displayed anywhere in your colum descriptors. I am not sure whether you actually wanted to display the subtotal in the client_name or acct_nbr column. If you want it to appear in the acct_nbr column then you likely need to make the acct_nbr field CHARACTER not numeric.

data WORK.TEST;
infile datalines;
input Client_Name_Group:$40. Client_Name:$40. Acct_Nbr:$21. Net_Balance:comma32.2;
datalines;
John John 1 2,500.00
John John 2 5,000.00
John John 3 250.00
John John 4 1,500.00
John John 5 2,300.00
John John 6 200.00
John John 7 900.00
John John 8 3,200.00
Jane Jane 9 750.00
Jane Jane 10 1,300.00
Jane Jane 11 2,700.00
Jane Jane 12 500.00
Jane Jane 13 400.00
Jane Jane 14 2,900.00
Jane Jane 15 1,700.00
Jane Jane 16 2,500.00
Jane Jane 17 2,900.00
Jane Jane 18 2,400.00
Jane Jane 19 3,700.00
Jane Jane 20 3,600.00
;

proc report nowd data = work.test
style(summary)=[fontweight=bold backgroundcolor=aliceblue];
column Client_Name_Group Client_Name Acct_Nbr Net_Balance;
define Client_Name_Group / GROUP noprint;
define Client_Name / DISPLAY 'Client Name' style(column)={tagattr='wraptext:no' width=100%};
define Acct_Nbr / DISPLAY 'Acct. #' style(column)={tagattr='wraptext:no' width=100%};
define Net_Balance / ANALYSIS SUM 'Net Balance' style(column)={tagattr='wraptext:no' width=100%};

break after Client_Name_Group / ol skip summarize ;
rbreak after / summarize SKIP UL OL;
compute after Client_Name_Group;
Acct_Nbr=catt('Subtotal of ',Client_Name_Group);
endcomp;

run;

HINT: values such as account numbers that you do not intend to do arithmetic with likely should be character.

Proc Report Subtotal Labels