topic Re: Numeric formatting Proc Tabulate in SAS Programming

Numeric formatting Proc Tabulate

Q1983 — Thu, 27 Oct 2016 21:19:15 GMT

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;

Re: Numeric formatting Proc Tabulate

Kurt_Bremser — Fri, 28 Oct 2016 09:02:45 GMT

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-data-AKA-generate/ta-p/258712

Most of us won't open a MS Office file from the web, and many even can't, because of security restrictions.

Re: Numeric formatting Proc Tabulate

Q1983 — Fri, 28 Oct 2016 13:50:36 GMT

Grand Total
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

Re: Numeric formatting Proc Tabulate

Cynthia_sas — Fri, 28 Oct 2016 19:40:51 GMT

Hi:

Typically, you use parentheses to control what columns are going to be spanned, as shown below.

cynthia