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.
... View more
