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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.