BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Q1983
Lapis Lazuli | Level 10

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

3 REPLIES 3
Q1983
Lapis Lazuli | Level 10
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

Cynthia_sas
SAS Super FREQ

Hi:

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

cynthia

compare_diff_parens_tab.png

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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