🔒 This topic is solved and locked.
Need further help from the community? Please
sign in and ask a new question.
1 ACCEPTED SOLUTION
Accepted Solutions
Hello,
Decimal alignment of frequencies with percentages is a classical problem in statistical programming. The bad news is, the JUST=D style attribute does not work for character variables, and this makes sense I mean how would SAS know how a value like '25 (32.0)' should be aligned since its not simple a numeric value. One solution to this problem, is to preprocess your data by using blank spaces to align the values. See the solution below.
*******************************************************************************************************;
* Source data *;
*******************************************************************************************************;
data final_1;
length AESOC AEPT cat $200 _NAME_ _LABEL_ UC1 CD1 TOTAL1 $15;
infile datalines dlm=',' dsd;
input AESOC $ AEPT $ sot cat $ _NAME_ $ _LABEL_ $ CD Total UC _page cnt UC1 $ CD1 $ TOTAL1 $;
datalines;
,,1,Number of Subjects with Any TEAE,COUNT,Frequency Count,84,103,19,1,1,19 (67.9),84 (80.0),103 (77.4)
Blood and lymphatic system disorders,,2,Blood And Lymphatic System Disorders,COUNT,Frequency Count,1,1,0,1,2,0 (0.0),1 (1.0),1 (0.8)
Blood and lymphatic system disorders,Infectious mononucleosis,2.1,Infectious Mononucleosis,COUNT,Frequency Count,1,1,0,1,3,0 (0.0),1 (1.0),1 (0.8)
Cardiac disorders,,2,Cardiac Disorders,COUNT,Frequency Count,1,3,2,1,4,2 (7.1),1 (1.0),3 (2.3)
Cardiac disorders,Palpitations,2.1,Palpitations,COUNT,Frequency Count,1,3,2,1,5,2 (7.1),1 (1.0),3 (2.3)
Ear and labyrinth disorders,,2,Ear And Labyrinth Disorders,COUNT,Frequency Count,1,1,0,1,6,0 (0.0),1 (1.0),1 (0.8)
Ear and labyrinth disorders,External ear cellulitis,2.1,External Ear Cellulitis,COUNT,Frequency Count,1,1,0,1,7,0 (0.0),1 (1.0),1 (0.8)
Epidermal and dermal conditions,,2,Epidermal And Dermal Conditions,COUNT,Frequency Count,1,1,0,1,8,0 (0.0),1 (1.0),1 (0.8)
Epidermal and dermal conditions,Erythema,2.1,Erythema,COUNT,Frequency Count,1,1,0,1,9,0 (0.0),1 (1.0),1 (0.8)
Eye disorders,,2,Eye Disorders,COUNT,Frequency Count,0,1,1,1,10,1 (3.6),0 (0.0),1 (0.8)
Eye disorders,Corneal abrasion,2.1,Corneal Abrasion,COUNT,Frequency Count,0,1,1,1,11,1 (3.6),0 (0.0),1 (0.8)
Gastrointestinal disorders,,2,Gastrointestinal Disorders,COUNT,Frequency Count,25,32,7,1,12,7 (25.0),25 (23.8),32 (24.1)
Gastrointestinal disorders,Abdominal and gastrointestinal infections,2.1,Abdominal And Gastrointestinal Infections,COUNT,Frequency Count,1,1,0,1,13,0 (0.0),1 (1.0),1 (0.8)
Gastrointestinal disorders,Abdominal discomfort,2.1,Abdominal Discomfort,COUNT,Frequency Count,1,1,0,1,14,0 (0.0),1 (1.0),1 (0.8)
Gastrointestinal disorders,Abdominal pain,2.1,Abdominal Pain,COUNT,Frequency Count,3,3,0,1,15,0 (0.0),3 (2.9),3 (2.3)
Gastrointestinal disorders,**bleep** abscess,2.1,**bleep** Abscess,COUNT,Frequency Count,1,1,0,2,,0 (0.0),1 (1.0),1 (0.8)
Gastrointestinal disorders,**bleep** fistula,2.1,**bleep** Fistula,COUNT,Frequency Count,1,1,0,2,1,0 (0.0),1 (1.0),1 (0.8)
;
run;
*******************************************************************************************************;
* Get the maximum number of digits for the frequencies and the maximum percent value across all groups*;
* Store the values in macros values *;
*******************************************************************************************************;
proc sql noprint;
create table temp as
select max(lengthn(scan(UC1,1)),lengthn(scan(CD1,1)),lengthn(scan(TOTAL1,1))) as CNTLEN,
max(input(scan(UC1,2,'()'),best.),input(scan(CD1,2,'()'),best.),input(scan(TOTAL1,2,'()'),best.)) as MAXPERCENT
from final_1;
select max(CNTLEN), max(MAXPERCENT) into :CNTLEN trimmed, :MAXPERCENT trimmed
from temp;
quit;
*******************************************************************************************************;
* To decimal align the values we add blank spaces for frequencies that have digits less then the *;
* maximum number of digit, by using the repeat function *;
* *;
* Example: *;
* Original | Updated | Final *;
* 120 | $120 | 120 *;
* 15 | $$50 | 15 *;
* 9 | $$$9 | 9 *;
* *;
* We do the same thing we the percent, then concatenate back the frequncies and percentages *;
*******************************************************************************************************;
data final_2;
set final_1;
if &MAXPERCENT < 100 then
PSPACE = 2;
else PSPACE = 3;
array COLS{*} UC1 CD1 TOTAL1;
do i = 1 to 3;
NUM = strip(scan(COLS{i},1));
PERCENT = strip(scan(COLS{i},2,'()'));
if lengthn(scan(strip(PERCENT),1,'.')) < PSPACE then
PERCENT = cats(repeat('$',PSPACE - 1 - lengthn(scan(strip(PERCENT),1,'.'))),PERCENT);
COLS{i} = tranwrd(cats(repeat('$',&CNTLEN - lengthn(NUM)),NUM,'$',cats('(',PERCENT,')')),'$',' ');
end;
CATSORT = (CAT ^= 'Number of Subjects with Any TEAE');
run;
*******************************************************************************************************;
* Decimal alignment of text only works for monospace fonts so we update the font to Courier New *;
* additional the asis attribute needs to be set to on, otherwise the blank spaces that we add will *;
* be trimmed in the output *;
*******************************************************************************************************;
/* Just in case the no template store with write access */
ods path (prepend) tmpl(update);
proc template;
define style mystyle;
parent=styles.rtf;
class fonts /
'TitleFont2' = ("Courier New",10pt)
'TitleFont' = ("Courier New",10pt)
'headingFont' = ("Courier New",10pt)
'docFont' = ("Courier New",10pt);
class table /
rules=groups
frame=hsides;
class header /
backgroundcolor=white
textalign=center;
class data /
outputwidth=1.8in
textalign=left;
class cell /
asis=on;
end;
run;
options orientation=landscape nodate nonumber;
ods escapechar='^';
ods rtf file='test.rtf' style=mystyle;
title j=r 'Page ^{thispage} of ^{lastpage}';
proc report data=final_2 missing split='!';
columns _PAGE CATSORT AESOC SOT CAT UC1 CD1 TOTAL1 AEPT;
define _PAGE / order order=internal noprint;
define CATSORT / order order=internal noprint;
define AESOC / order order=internal noprint;
define CAT / order order=internal 'System Organ Class /! Preferred Term' style(header) = {just=l} style(column)={cellwidth=3.6in};
define SOT / order order=internal noprint;
define UC1 / 'Group UC' style(column)={marginleft=10mm};
define CD1 / 'Group CD' style(column)={marginleft=10mm};
define TOTAL1 / 'Total'style(column)={marginleft=10mm};
define AEPT / noprint;
compute before AESOC;
line '';
endcomp;
compute AEPT;
if ^missing(AEPT) then
call define('CAT','style','style={leftmargin=5mm}');
endcomp;
break after _PAGE / page;
run;
ods rtf close;
I hope this helps and if you have any further questions feel free to reach out.
Hi:
Since you did not provide data, no one can run your code. I notice that you are using HEADLINE and HEADSKIP which are not used at all by any of the ODS destinations, since those are LISTING only options.I also recommend using PT sizes as the unit of measure with RTF and PDF destinations. Otherwise, you force both destinations to convert into the unit of measure for each destination. You're specifying a relative size without any unit of measure and both RTF and PDF have to convert that relative unit (designed for HTML) to an absolute unit of measure.
Using a subset of SASHELP.SHOES and making sure that I have mixed decimal and non-decimal values in the data, I was able to make a _page variable and do page breaking (I only need to make 4 pages to illustrate the use of the ESCAPECHAR functions). All my output shows the proper page x of y page numbering when I open the TAGSETS.RTF output in Word and look at the output in Print Preview. I simplified my report to the essentials to show that Page X of Y worked and decimal alignment both worked as shown below.
Here's my output:
Cynthia
Here's the simplified code and data that I used:
ods path work.tmp(update) sasuser.templat(update) sashelp.tmplmst(read);
proc template;
define style gaps;
parent=styles.journal;
class parskip / fontsize = 1pt;
end;
run;
data final_test;
length Region $25 Product $15;
infile datalines dlm=',' dsd;
input _page Region $ Product $ Sales : comma. Inventory : comma. Returns : comma.;
datalines;
1,"Africa","Boot","$29,761.11","$191,821.11","$769.11"
1,"Africa","Slipper","$39,452.22","$130,025.22","$1,565.22"
1,"Africa","Boot","$16,282","$66,017","$844"
1,"Canada","Women's Dress","$12,601","$54,677","$488"
1,"Canada","Men's Casual","$353,361","$671,837","$20,470"
1,"Central America/Caribbean","Men's Dress","$30,157","$78,950","$1,012"
1,"Eastern Europe","Slipper","$127,033","$520,271","$3,983"
1,"Middle East","Sport Shoe","$449","$1,836","$43"
1,"Pacific","Women's Dress","$42,760","$158,688","$1,278"
1,"Pacific","Boot","$31,503.33","$110,179.33","$1,449.33"
1,"South America","Slipper","$11,759","$23,822","$939"
1,"South America","Sport Shoe","$8,365","$39,256","$361"
1,"United States","Women's Dress","$293,313","$754,157","$10,194"
1,"United States","Boot","$70,790","$226,678","$3,039"
1,"Western Europe","Men's Casual","$175,694","$595,118","$9,845"
1,"Western Europe","Men's Dress","$54,791","$181,739","$1,468"
2,"Africa","Men's Casual","$67,242.44","$118,036.44","$2,284.44"
2,"Africa","Sport Shoe","$5,172","$29,368","$139"
2,"Africa","Men's Dress","$8,587","$20,877","$363"
2,"Canada","Boot","$40,213","$240,132","$1,472"
2,"Canada","Men's Dress","$757,798.55","$1,847,559.55","$16,833.55"
2,"Central America/Caribbean","Sandal","$52,233","$175,201","$2,127"
2,"Eastern Europe","Sport Shoe","$21,464","$110,822","$859"
2,"Middle East","Women's Casual","$226,514","$447,377","$5,966"
2,"Pacific","Boot","$32,640","$188,437","$1,237"
2,"Pacific","Men's Casual","$128,309","$264,891","$5,285"
2,"South America","Sport Shoe","$3,545","$6,103","$229"
2,"South America","Women's Casual","$28,343","$66,442","$1,374"
2,"United States","Boot","$85,932","$347,252","$3,283"
2,"United States","Men's Casual","$65,842","$112,538","$3,395"
2,"Western Europe","Men's Dress","$161,679","$468,267","$5,373"
2,"Western Europe","Slipper","$12,127","$10,234","$500"
3,"Africa","Men's Dress","$76,793.66","$136,273.66","$2,433.66"
3,"Africa","Women's Dress","$42,682","$120,127","$966"
3,"Africa","Sandal","$16,289","$47,406","$1,175"
3,"Canada","Men's Casual","$53,929","$187,155","$2,241"
3,"Canada","Sandal","$5,120.77","$56,325.77","$294.77"
3,"Central America/Caribbean","Slipper","$96,113","$340,122","$4,028"
3,"Eastern Europe","Women's Casual","$104,119","$211,220","$2,325"
3,"Middle East","Women's Dress","$241,820","$611,686","$6,167"
3,"Pacific","Men's Casual","$24,733","$45,172","$614"
3,"Pacific","Men's Dress","$144,151","$464,158","$4,414"
3,"South America","Women's Casual","$15,470","$19,479","$382"
3,"South America","Women's Dress","$21,434","$63,913","$1,213"
3,"United States","Men's Casual","$177,010","$401,199","$6,336"
3,"United States","Men's Dress","$132,397","$274,310","$3,153"
3,"Western Europe","Sandal","$977","$4,618","$62"
3,"Western Europe","Women's Casual","$50,146","$113,870","$1,216"
4,"Africa","Sandal","$62,819.88","$204,284.88","$1,861.88"
4,"Africa","Boot","$19,282","$105,370","$700"
4,"Africa","Slipper","$34,955","$87,438","$1,320"
4,"Canada","Men's Dress","$112,009","$355,170","$3,713"
4,"Canada","Slipper","$700,513","$2,520,085","$21,247"
4,"Central America/Caribbean","Sport Shoe","$2,196","$23,183","$59"
4,"Eastern Europe","Women's Dress","$72,671","$196,973","$2,227"
4,"Middle East","Boot","$90,972.99","$403,259.99","$4,049.99"
4,"Pacific","Men's Dress","$4,231","$346,780","$350"
4,"Pacific","Sandal","$15,759","$65,223","$620"
4,"South America","Women's Dress","$21,261","$31,081","$964"
4,"South America","Boot","$14,907","$39,222","$939"
4,"United States","Men's Dress","$147,670","$348,869","$4,962"
4,"United States","Sandal","$6,596","$18,768","$181"
4,"Western Europe","Slipper","$243,342","$928,112","$7,940"
4,"Western Europe","Women's Dress","$11,385","$59,192","$273"
;
run;
ods escapechar='^';
options orientation=landscape nodate nonumber pageno=1;
ods tagsets.rtf file="C:\temp\test_pagexofy.rtf" style=gaps;
proc report data=final_test nowd split='~' missing ;
title j=c 'My Title' j=r 'Page ^{thispage} of ^{lastpage}';
column _page region product sales inventory returns;
define _page / order;
define sales /style(column)={width=1.5in just=d} 'Decimal';
define inventory / style(column)={width=1.5in just=r} 'Right';
define returns / style(column)={width=1.5in just=c} 'Center';
break after _page / page;
run;
ods tagsets.rtf close;Hello,
Decimal alignment of frequencies with percentages is a classical problem in statistical programming. The bad news is, the JUST=D style attribute does not work for character variables, and this makes sense I mean how would SAS know how a value like '25 (32.0)' should be aligned since its not simple a numeric value. One solution to this problem, is to preprocess your data by using blank spaces to align the values. See the solution below.
*******************************************************************************************************;
* Source data *;
*******************************************************************************************************;
data final_1;
length AESOC AEPT cat $200 _NAME_ _LABEL_ UC1 CD1 TOTAL1 $15;
infile datalines dlm=',' dsd;
input AESOC $ AEPT $ sot cat $ _NAME_ $ _LABEL_ $ CD Total UC _page cnt UC1 $ CD1 $ TOTAL1 $;
datalines;
,,1,Number of Subjects with Any TEAE,COUNT,Frequency Count,84,103,19,1,1,19 (67.9),84 (80.0),103 (77.4)
Blood and lymphatic system disorders,,2,Blood And Lymphatic System Disorders,COUNT,Frequency Count,1,1,0,1,2,0 (0.0),1 (1.0),1 (0.8)
Blood and lymphatic system disorders,Infectious mononucleosis,2.1,Infectious Mononucleosis,COUNT,Frequency Count,1,1,0,1,3,0 (0.0),1 (1.0),1 (0.8)
Cardiac disorders,,2,Cardiac Disorders,COUNT,Frequency Count,1,3,2,1,4,2 (7.1),1 (1.0),3 (2.3)
Cardiac disorders,Palpitations,2.1,Palpitations,COUNT,Frequency Count,1,3,2,1,5,2 (7.1),1 (1.0),3 (2.3)
Ear and labyrinth disorders,,2,Ear And Labyrinth Disorders,COUNT,Frequency Count,1,1,0,1,6,0 (0.0),1 (1.0),1 (0.8)
Ear and labyrinth disorders,External ear cellulitis,2.1,External Ear Cellulitis,COUNT,Frequency Count,1,1,0,1,7,0 (0.0),1 (1.0),1 (0.8)
Epidermal and dermal conditions,,2,Epidermal And Dermal Conditions,COUNT,Frequency Count,1,1,0,1,8,0 (0.0),1 (1.0),1 (0.8)
Epidermal and dermal conditions,Erythema,2.1,Erythema,COUNT,Frequency Count,1,1,0,1,9,0 (0.0),1 (1.0),1 (0.8)
Eye disorders,,2,Eye Disorders,COUNT,Frequency Count,0,1,1,1,10,1 (3.6),0 (0.0),1 (0.8)
Eye disorders,Corneal abrasion,2.1,Corneal Abrasion,COUNT,Frequency Count,0,1,1,1,11,1 (3.6),0 (0.0),1 (0.8)
Gastrointestinal disorders,,2,Gastrointestinal Disorders,COUNT,Frequency Count,25,32,7,1,12,7 (25.0),25 (23.8),32 (24.1)
Gastrointestinal disorders,Abdominal and gastrointestinal infections,2.1,Abdominal And Gastrointestinal Infections,COUNT,Frequency Count,1,1,0,1,13,0 (0.0),1 (1.0),1 (0.8)
Gastrointestinal disorders,Abdominal discomfort,2.1,Abdominal Discomfort,COUNT,Frequency Count,1,1,0,1,14,0 (0.0),1 (1.0),1 (0.8)
Gastrointestinal disorders,Abdominal pain,2.1,Abdominal Pain,COUNT,Frequency Count,3,3,0,1,15,0 (0.0),3 (2.9),3 (2.3)
Gastrointestinal disorders,**bleep** abscess,2.1,**bleep** Abscess,COUNT,Frequency Count,1,1,0,2,,0 (0.0),1 (1.0),1 (0.8)
Gastrointestinal disorders,**bleep** fistula,2.1,**bleep** Fistula,COUNT,Frequency Count,1,1,0,2,1,0 (0.0),1 (1.0),1 (0.8)
;
run;
*******************************************************************************************************;
* Get the maximum number of digits for the frequencies and the maximum percent value across all groups*;
* Store the values in macros values *;
*******************************************************************************************************;
proc sql noprint;
create table temp as
select max(lengthn(scan(UC1,1)),lengthn(scan(CD1,1)),lengthn(scan(TOTAL1,1))) as CNTLEN,
max(input(scan(UC1,2,'()'),best.),input(scan(CD1,2,'()'),best.),input(scan(TOTAL1,2,'()'),best.)) as MAXPERCENT
from final_1;
select max(CNTLEN), max(MAXPERCENT) into :CNTLEN trimmed, :MAXPERCENT trimmed
from temp;
quit;
*******************************************************************************************************;
* To decimal align the values we add blank spaces for frequencies that have digits less then the *;
* maximum number of digit, by using the repeat function *;
* *;
* Example: *;
* Original | Updated | Final *;
* 120 | $120 | 120 *;
* 15 | $$50 | 15 *;
* 9 | $$$9 | 9 *;
* *;
* We do the same thing we the percent, then concatenate back the frequncies and percentages *;
*******************************************************************************************************;
data final_2;
set final_1;
if &MAXPERCENT < 100 then
PSPACE = 2;
else PSPACE = 3;
array COLS{*} UC1 CD1 TOTAL1;
do i = 1 to 3;
NUM = strip(scan(COLS{i},1));
PERCENT = strip(scan(COLS{i},2,'()'));
if lengthn(scan(strip(PERCENT),1,'.')) < PSPACE then
PERCENT = cats(repeat('$',PSPACE - 1 - lengthn(scan(strip(PERCENT),1,'.'))),PERCENT);
COLS{i} = tranwrd(cats(repeat('$',&CNTLEN - lengthn(NUM)),NUM,'$',cats('(',PERCENT,')')),'$',' ');
end;
CATSORT = (CAT ^= 'Number of Subjects with Any TEAE');
run;
*******************************************************************************************************;
* Decimal alignment of text only works for monospace fonts so we update the font to Courier New *;
* additional the asis attribute needs to be set to on, otherwise the blank spaces that we add will *;
* be trimmed in the output *;
*******************************************************************************************************;
/* Just in case the no template store with write access */
ods path (prepend) tmpl(update);
proc template;
define style mystyle;
parent=styles.rtf;
class fonts /
'TitleFont2' = ("Courier New",10pt)
'TitleFont' = ("Courier New",10pt)
'headingFont' = ("Courier New",10pt)
'docFont' = ("Courier New",10pt);
class table /
rules=groups
frame=hsides;
class header /
backgroundcolor=white
textalign=center;
class data /
outputwidth=1.8in
textalign=left;
class cell /
asis=on;
end;
run;
options orientation=landscape nodate nonumber;
ods escapechar='^';
ods rtf file='test.rtf' style=mystyle;
title j=r 'Page ^{thispage} of ^{lastpage}';
proc report data=final_2 missing split='!';
columns _PAGE CATSORT AESOC SOT CAT UC1 CD1 TOTAL1 AEPT;
define _PAGE / order order=internal noprint;
define CATSORT / order order=internal noprint;
define AESOC / order order=internal noprint;
define CAT / order order=internal 'System Organ Class /! Preferred Term' style(header) = {just=l} style(column)={cellwidth=3.6in};
define SOT / order order=internal noprint;
define UC1 / 'Group UC' style(column)={marginleft=10mm};
define CD1 / 'Group CD' style(column)={marginleft=10mm};
define TOTAL1 / 'Total'style(column)={marginleft=10mm};
define AEPT / noprint;
compute before AESOC;
line '';
endcomp;
compute AEPT;
if ^missing(AEPT) then
call define('CAT','style','style={leftmargin=5mm}');
endcomp;
break after _PAGE / page;
run;
ods rtf close;I hope this helps and if you have any further questions feel free to reach out.
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 webinar
Classroom Training Available!
Select SAS Training centers are offering in-person courses. View upcoming courses for:
-
1942 views
-
0 likes
-
-
