DATA Step, Macro, Functions and more

Numeric formatting Proc Tabulate

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 141
Accepted Solution

Numeric formatting Proc Tabulate

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;

  1. 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;


Accepted Solutions
Solution
‎10-28-2016 03:49 PM
SAS Super FREQ
Posts: 8,862

Re: Numeric formatting Proc Tabulate

Hi:

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

cynthia

compare_diff_parens_tab.png

View solution in original post


All Replies
Super User
Posts: 7,760

Re: Numeric formatting Proc Tabulate

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.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 141

Re: Numeric formatting Proc Tabulate

Posted in reply to KurtBremser
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

Solution
‎10-28-2016 03:49 PM
SAS Super FREQ
Posts: 8,862

Re: Numeric formatting Proc Tabulate

Hi:

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

cynthia

compare_diff_parens_tab.png

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 558 views
  • 1 like
  • 3 in conversation