In the attached starting at cell V Grand total does not occupy cell V through Y. How can I get it to occupy the entire area in yellow
I looked on sas communities for variable formatting in proc tabulate but found very little. In the attached I want the percentage columns to display a format like this 1.67% and the dollar figures like this $ 500,000 I tried inserting f=8.2 after the *colpctSum='% in Dollars' I get the value with N as a column however when I use keylabel n=’’ it does not disappear
Proc tabulate data=exc_cnt_1 order= data format=10. S=[cellwidth=80];
Class prog_ctgy CODEDESC;
Var GROSS_LN_AMT ln_cnt;
Table CODEDESC=' ' all={label='Total' S=[background = lightblue cellwidth=80]} *[STYLE=[Font_Weight=BOLD]],
prog_ctgy*(ln_cnt=''*sum='Exceptions Units' ln_cnt=''*colpctn='% in Units'
GROSS_LN_AMT=''*sum='Exceptions$' GROSS_LN_AMT='' *colpctSum='% in Dollars' )
all={label='Grand Total' S=[background = lightblue]} *[STYLE=[Font_Weight=BOLD]]
*ln_cnt =' '*sum='Total Exceptions Units ' GROSS_LN_AMT =' '*sum='Total Exceptions Dollars$'
ln_cnt=''*colpctn='% in Units' GROSS_LN_AMT=''*colpctSum='% in Dollars' / box='Exception Codes';
TITLE 'Summary';
run;
Hi:
Typically, you use parentheses to control what columns are going to be spanned, as shown below.
cynthia
Please post your example data in a data step, as described in https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...
Most of us won't open a MS Office file from the web, and many even can't, because of security restrictions.
Grand Total | |||
Total Exceptions Units | Total Exceptions Dollars$ | % in Units | % in Dollars |
28 | 27754468 | 3 | 4 |
108 | 77502274 | 11 | 10 |
97 | 62191848 | 9 | 8 |
48 | 41659802 | 5 | 5 |
166 | 1.21E+08 | 16 | 15 |
80 | 51996183 | 8 | 7 |
46 | 64446183 | 4 | 8 |
58 | 64995170 | 6 | 8 |
10 | 9024241 | 1 | 1 |
The only assistance needed is how to get the Grand total to show on top across the four columns listed. Here is the datastep
data exc_cnt_1(keep= prog_ctgy CODEDESC CODESHORTDESC GROSS_LN_AMT ln_cnt);
set loan7;
if CODEDESC ne '' then ln_cnt = 1; else ln_cnt = 0;
if idx >0;
/*if ln_no = '4000012619';*/
run;
proc sort data= exc_cnt_1;by CODEDESC prog_ctgy;run;
Here is the proc tabulate
Proc tabulate data=exc_cnt_1 order= data format=10. S=[cellwidth=80];
Class prog_ctgy CODEDESC;
Var GROSS_LN_AMT ln_cnt;
Table CODEDESC=' ' all={label='Total' S=[background = lightblue cellwidth=80]} *[STYLE=[Font_Weight=BOLD]],
prog_ctgy*(ln_cnt=''*sum='Exceptions Units' ln_cnt=''*colpctn='% of Units'*f=number8.2
GROSS_LN_AMT=''*sum='Exceptions$' *f=dollar16.2 GROSS_LN_AMT='' *colpctSum='% of Dollars'*f=number8.2 )
all={/*label='Grand Total'*/ S=[background = lightblue]} *[STYLE=[Font_Weight=BOLD]]
*ln_cnt =' '*sum='Total Exceptions Units' GROSS_LN_AMT =' '*sum='Total Exceptions Dollars$' *f=dollar16.2
ln_cnt=''*colpctn='Total % in Units' *f=number8.2 GROSS_LN_AMT=''*colpctSum='Total % in Dollars' *f=number8.2
/ box='Exception Codes';
TITLE 'Summary';
label colpctn = '% of Units';
keylabel n='';
run;
Grand Total is commented out here however in the output it only shows above Total Exceptions Units and not the remaining 3 columns. Grand total should display across the 4 columns on top of it
Hi:
Typically, you use parentheses to control what columns are going to be spanned, as shown below.
cynthia
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.
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.