How to Indent Selected Table Cells using NBSPACE

[Pages:5]How to Indent Selected Table Cells using NBSPACE

Roberta J Glass, Abt Associates, Cambridge, MA

ABSTRACT

Indenting selected lines in a table produced with the SAS? Proc Report procedure can be difficult. Using the escape character function NBSPACE to inline format table cells is one solution. This Quick Tip illustrates the difficulty of indenting selected lines and a solution that works when creating output tables with ODS.

INTRODUCTION

I wanted to create a table in which total lines were left justified and sub-total lines were indented a few spaces. I tried a number of methods, one of which was to create a format for the type of line with spaces to indent the subtotal lines. In both excel and rtf output the results were the same ? all of the lines were left justified. Then I attended a session at NESUG about inline formatting using escape character functions and learned a way to accomplish my goal. The NBSPACE escape character function was the perfect solution. First I will show the problem that I encountered, and then the code to produce the desired indenting in tables output through ODS to excel and rtf formats.

THE PROBLEM

My example data set contains 16 observations consisting of four total records for types of home health visits ? Skilled Nursing, Physical Therapy, Occupational Therapy, and Speech Therapy. Each total record is followed by 2 or more sub-total records which break the total down by the type of service and provider. The variable visit type is a numeric variable that ranges from 1 to 16 and corresponds to the observation number. The goal was to produce a table in which the first column looks like this:

Type of Visit Skilled Nursing Total

RN/LPN - direct care RN - care plan RN/LPN - observation RN/LPN - training Physical Therapy Total PT Therapist PT Assistant PT Maintenance Occupational Therapy Total OT Therapist OT Assistant OT Maintenance Speech Therapy Total ST Therapist ST Maintenance

The code in Figure 1 uses a proc format statement to format the variable visit_type in an attempt to indent just the sub-total lines by 5 spaces.

1

Figure 1.

proc format;

value vt

1 = 'Skilled Nursing Total'

2 = '

RN/LPN - direct care'

3 = '

RN

- care plan'

4 = '

RN/LPN - observation'

5 = '

RN/LPN - training'

6 = 'Physical Therapy Total'

7 = '

PT Therapist'

8 = '

PT Assistant'

9 = '

PT Maintenance'

10 = 'Occupational Therapy Total'

11 = '

OT Therapist'

12 = '

OT Assistant'

13 = '

OT Maintenance'

14 = 'Speech Therapy Total'

15 = '

ST Therapist'

16 = '

ST Maintenance'

;

ods html file="Table_spaces_Example1.xls" path=odsout style=styles.journal2; ods escapechar="^"; proc report data=table_lines nowd;

column visit_type visit_mean visit_sum charge_mean charge_sum;

define visit_type /display 'Type of Visit' format=vt. left; define visit_mean /display 'Mean Visits per Person'

format=comma12.1; define visit_sum /display 'Total Visits' format=comma12.; define charge_mean/display 'Mean Charges per Visit'

format=dollar12.2; define charge_sum /display 'Total Charges'

format=dollar16.2;

title1 'Proc Report - attempt to indent using spaces'; run; ods html close;

Figure 2 shows that this method did not work. Excel very kindly removes the leading blanks from the sub-total lines, leaving every line of the table left justified. I also tried creating a character variable with leading blanks embedded in the text, but again the leading blanks disappeared and the entire table was left justified. I also checked to see whether writing the table to an RTF file would work, but again failed to indent the sub-total lines.

THE SOLUTION - NBSPACE

And that was when a trip to NESUG gave me a glimmer of hope. As I listened to a presentation on escape characters, NBSPACE was mentioned. On my return to the office I replaced the leading spaces in my format with this handy escape character function (Figure 3). I also added a command to define the ODS escape character as ^ (Figure 4) and my problem was solved. The changes to the code are in bold.

2

Figure 2. Proc Report .xls table - attempt to indent using spaces

Type of Visit

Skilled Nursing Total RN/LPN - direct care RN - care plan RN/LPN - observation RN/LPN - training Physical Therapy Total PT Therapist PT Assistant PT Maintenance Occupational Therapy Total OT Therapist OT Assistant OT Maintenance Speech Therapy Total ST Therapist ST Maintenance

Mean Visits per Per-

son 218

152

3

22

41

111

Total Visits

2,473,330 1,722,892

34,556 253,644 462,239 1,256,184

Mean Charges per Vis-

it

$1,360.29 $945.39 $18.69 $141.62 $254.59 $741.06

Total Charges

$15,431,130.70 $10,724,556.74

$211,981.10 $1,606,553.72 $2,888,039.14 $8,406,585.76

79 896,899

30 340,522

2

18,763

26 295,697

$536.58 $194.21

$10.27 $175.19

$6,086,962.08 $2,203,173.18

$116,450.50 $1,987,369.32

21 242,686

4

48,777

4

48,777

5

54,823

5

54,023

5

54,023

$145.25 $27.65 $2.29 $33.38 $32.92 $0.46

$1,647,697.40 $313,695.63 $25,976.28 $378,672.94 $373,458.60 $5,214.34

Figure 3.

proc format;

value vtnbsp 1 = 'Skilled Nursing Total' 2 = '^{style ^{NBSPACE 5}}RN/LPN - direct care' 3 = '^{style ^{NBSPACE 5}}RN - care plan' 4 = '^{style ^{NBSPACE 5}}RN/LPN - observation' 5 = '^{style ^{NBSPACE 5}}RN/LPN - training' 6 = 'Physical Therapy Total' 7 = '^{style ^{NBSPACE 5}}PT Therapist' 8 = '^{style ^{NBSPACE 5}}PT Assistant' 9 = '^{style ^{NBSPACE 5}}PT Maintenance'

10 = 'Occupational Therapy Total' 11 = '^{style ^{NBSPACE 5}}OT Therapist' 12 = '^{style ^{NBSPACE 5}}OT Assistant' 13 = '^{style ^{NBSPACE 5}}OT Maintenance' 14 = 'Speech Therapy Total' 15 = '^{style ^{NBSPACE 5}}ST Therapist' 16 = '^{style ^{NBSPACE 5}}ST Maintenance' ; run;

3

Figure 4.

ods html file="Table_spaces_Example2.xls" path=odsout style=styles.journal2; ods escapechar="^";

proc report data=table_lines nowd list; column visit_type visit_mean visit_sum charge_mean charge_sum;

define visit_type /display 'Type of Visit' format= vtnbsp. left; define visit_mean /display 'Mean Visits per Person'

format=comma12.1; define visit_sum /display 'Total Visits' format=comma12.; define charge_mean/display 'Mean Charges per Visit'

format=dollar12.2; define charge_sum /display 'Total Charges'

format=dollar16.2;

title1 'Proc Report - indented using NBSPACE'; run; ods html close;

The resulting table has the desired indenting. This code works for both excel and rtf formatted tables. For excel files the code inserted into the format can be simplified from ^{style ^{NBSPACE 5}} to ^{NBSPACE 5}. In this example 5 is the number of spaces to insert, if no number is specified the default is one space.

Proc Report .xls table indented using NBSPACE

Type of Visit

Skilled Nursing Total RN/LPN - direct care RN - care plan RN/LPN - observation RN/LPN - training

Physical Therapy Total PT Therapist PT Assistant PT Maintenance

Occupational Therapy Total

OT Therapist OT Assistant OT Maintenance Speech Therapy Total ST Therapist ST Maintenance

Mean Visits per Per-

son 218 152 3 22 41 111 79 30 2 26

21 4 4 5 5 5

Total Visits

2,473,330 1,722,892

34,556 253,644 462,239 1,256,184 896,899 340,522

18,763 295,697

242,686 48,777 48,777 54,823 54,023 54,023

Mean Charges per Visit

$1,360.29 $945.39 $18.69 $141.62 $254.59 $741.06 $536.58 $194.21 $10.27 $175.19

$145.25 $27.65 $2.29 $33.38 $32.92 $0.46

Total Charges

$15,431,130.70 $10,724,556.74

$211,981.10 $1,606,553.72 $2,888,039.14 $8,406,585.76 $6,086,962.08 $2,203,173.18

$116,450.50 $1,987,369.32

$1,647,697.40 $313,695.63 $25,976.28 $378,672.94 $373,458.60 $5,214.34

4

CONCLUSION

The inline formatting escape character function NBSPACE can be used to indent selected lines in an excel table. This solution is easy to implement and produces a table that is easier for the reader to interpret.

ACKNOWLEDGEMENTS

SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ? indicates USA registration. Thanks to Louise Hadden for pointing out that NBSPACE was the solution to the problem I had discussed with her.

CONTACT INFORMATION

Your comments and questions are valued and encouraged. Contact the author at: Roberta J. Glass Abt Associates 55 Wheeler St Cambridge, MA 02138-1168 Work Phone: 617-520-3019 Email: Roberta_Glass@

5

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download