Good day
I am struggling to align counts and decimals in this table and also to have page Xof Y.
Please advise.
I have attached the tables observations to be used as example.
Tables data:
COHORT AESOC AEPT sot cat _NAME_ _LABEL_ Not_related Possible Probable Unlikely Total sort _page cnt Not_Related1 Unlikely1 Possible1 Probable1 TOTAL1 _empty
UC Ulcerative Colitis (N=28) COUNT Frequency Count 4 11 0 7 22 1 1 1 4 (14.3) 7 (25.0) 11 (39.3) 0 (0.0) 22 (16.5)
UC 1 Number of Subjects with Any TEAE COUNT Frequency Count 2 10 0 7 19 1 1 2 2 (7.1) 7 (25.0) 10 (35.7) 0 (0.0) 19 (14.3)
UC Cardiac disorders 2 Cardiac Disorders COUNT Frequency Count 0 1 0 1 2 1 1 3 0 (0.0) 1 (3.6) 1 (3.6) 0 (0.0) 2 (1.5)
UC Cardiac disorders Palpitations 2.1 Palpitations COUNT Frequency Count 0 1 0 1 2 1 1 4 0 (0.0) 1 (3.6) 1 (3.6) 0 (0.0) 2 (1.5)
UC Eye disorders 2 Eye Disorders COUNT Frequency Count 1 0 0 0 1 1 1 5 1 (3.6) 0 (0.0) 0 (0.0) 0 (0.0) 1 (0.8)
UC Eye disorders Corneal abrasion 2.1 Corneal Abrasion COUNT Frequency Count 1 0 0 0 1 1 1 6 1 (3.6) 0 (0.0) 0 (0.0) 0 (0.0) 1 (0.8)
UC Gastrointestinal disorders 2 Gastrointestinal Disorders COUNT Frequency Count 2 3 0 2 7 1 1 7 2 (7.1) 2 (7.1) 3 (10.7) 0 (0.0) 7 (5.3)
UC Gastrointestinal disorders Colitis ulcerative 2.1 Colitis Ulcerative COUNT Frequency Count 2 0 0 0 2 1 1 8 2 (7.1) 0 (0.0) 0 (0.0) 0 (0.0) 2 (1.5)
UC Gastrointestinal disorders Frequent bowel movements 2.1 Frequent Bowel Movements COUNT Frequency Count 0 0 0 1 1 1 1 9 0 (0.0) 1 (3.6) 0 (0.0) 0 (0.0) 1 (0.8)
UC Gastrointestinal disorders Nausea 2.1 Nausea COUNT Frequency Count 0 2 0 0 2 1 1 10 0 (0.0) 0 (0.0) 2 (7.1) 0 (0.0) 2 (1.5)
UC Gastrointestinal disorders Tooth infection 2.1 Tooth Infection COUNT Frequency Count 0 1 0 0 1 1 1 11 0 (0.0) 0 (0.0) 1 (3.6) 0 (0.0) 1 (0.8)
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:
What code have you tried? What is your desired destination. What kind of report output do you want? Page X of Y page numbering is only performed in ODS RTF and ODS PDF destinations, using ODS ESCAPECHAR. So if you have
ODS ESCAPECHAR='^';
then
title j=c 'My Title' j=r 'Page ^{thispage} of ^{lastpage}';
should work.
For decimal alignment, you need to look at style overrides regarding justification options. Some destinations support just=d or textalign=d as a style override.
Cynthia
Good day Cynthia
Please see below my code:
/************REPORT************/
options orientation=landscape nodate topmargin=.5in bottommargin=.1in rightmargin=.40in leftmargin=.5in pageno=1;
proc template;
define style gaps;
parent=styles.journal;
class parskip / fontsize = 1pt;
end;
run;
ods escapechar='^';
ods listing close;
ods tagsets.rtf file="C:\Test.rtf"
style=gaps;
proc report data=final_1 nowd headline headskip split='~' missing style ( report )=[frame=hsides outputwidth = 10in background = white
font_face='Courier New' font_size=4]
style(column)={just=left font_face='Courier New' background=white foreground=black
font_size=3 }
style(header)={just=center font_face='Courier New' cellheight=.4in font_size=3
foreground=black
background=white font_style=roman vjust=t} ;
column _page sort COHORT AESOC AEPT sot cnt cat ( /*^{style[textdecoration=underline]*/
'^S={borderbottomwidth=0.15}Strongest Relationship'
Not_Related1 Unlikely1 Possible1 Probable1)_empty (TOTAL1);
define _page / order order=internal noprint;
define sort / order order=internal noprint;
define COHORT / ORDER order=internal noprint ;
define AESOC / ORDER order=internal noprint ;
define AEPT / order order=internal noprint;
define sot / order order=internal noprint;
define CNT / order order=internal noprint;
define CAT / display "System Organ Class / Preferred Term" style(header)={just=left cellwidth=4in}
;
define Not_Related1 / display "Not Related~N(%)" style(header)={just=CENTER cellwidth=0.9in}
style(column)={just=CENTER cellwidth=0.9in font_weight=medium
font_face='Courier New' font_size=3};
define Unlikely1 / display "Unlikely~N(%)" style(header)={just=CENTER cellwidth=1in}
style(column)={just=CENTER cellwidth=1in font_weight=medium
font_face='Courier New' font_size=3};
define Possible1 / display "Possible~N(%)" style(header)={just=CENTER cellwidth=1in}
style(column)={just=CENTER cellwidth=1in font_weight=medium
font_face='Courier New' font_size=3};
define Probable1 / display "Probable~N(%)" style(header)={just=CENTER cellwidth=1in}
style(column)={just=CENTER cellwidth=1in font_weight=medium
font_face='Courier New' font_size=3};
define _empty / order order=data noprint;
define TOTAL1 / display "Total~N(%)" style(header)={just=CENTER cellwidth=1in}
style(column)={just=dec cellwidth=1in font_weight=medium
font_face='Courier New' font_size=3};
compute CAT;
if sot>=2.1 then do;
call define(_col_,'style','style={leftmargin=.15in}');
end;
endcomp;
compute before _page;
line " ";
endcomp;
compute after AESOC;
line " ";
endcomp;
Break after _page/ page;
run;
ods tagsets.rtf close;
ods listing;
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;
Hi Cynthia
I have tried out the options on my code but they seem to not output accordingly.
Please see below the data and code i used. The columns i am trying to align is CD1 UC1 TOTAL1.
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_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;
ods escapechar='^';
options orientation=landscape nodate nonumber pageno=1;
ods tagsets.rtf file="C:\Users\Dinkepile\Desktop\SAP\test\test_pagexofy.rtf" style=gaps;
proc report data=final_1 nowd split='~' missing style ( report )=[frame=hsides outputwidth = 10in background = white
font_face='Courier New' font_size=4]
style(column)={just=CENTER font_face='Courier New' background=white foreground=black
font_size=3 cellwidth=.6in}
style(header)={just=center font_face='Courier New' cellheight=.5in font_size=3
foreground=black cellwidth=.6in
background=white font_style=roman vjust=t} ;
title j=c 'My Title' j=r 'Page ^{thispage} of ^{lastpage}';
column _page AESOC AEPT sot cnt cat ( UC1 CD1 TOTAL1);
define _page / order order=internal noprint;
define AESOC / ORDER order=internal noprint ;
define AEPT / order order=internal noprint;
define sot / order order=internal noprint;
define CNT / order order=internal noprint;
define CAT / display "System Organ Class / Preferred Term" style(header)={just=left cellwidth=3in}
style(column)={just=left cellwidth=3in
font_face='Courier New' font_size=3};
define UC1 / display "UC (N=28))~N(%)" style(header)={just=CENTER cellwidth=0.7in}
style(column)={just=CENTER cellwidth=0.5in font_weight=medium
font_face='Courier New' font_size=3};
define CD1 / display "CD (N=103)~N(%)" style(header)={just=CENTER cellwidth=0.7in}
style(column)={just=CENTER cellwidth=0.5in
font_face='Courier New' font_size=3};
define TOTAL1 / display "All Subjects (N=131)~N(%)" style(header)={just=CENTER cellwidth=1.2in}
style(column)={just=CENTER cellwidth=1.2in
font_face='Courier New' font_size=3 protectspecialchars = off};
compute CAT;
if sot>=2.1 then do;
call define(_col_,'style','style={leftmargin=.15in}');
end;
endcomp;
compute before _page;
line " ";
endcomp;
compute after AESOC;
line " ";
endcomp;
Break after _page/ page;
run;
ods tagsets.rtf close;
Hi:
It looks to me like you have JUST=CENTER for those variables and they are character variables. I believe that JUST=D might only work for numeric variables. You can check this with Tech Support. For most of the demographic reports that I've seen, usually when you have numbers like those below,
103 (77.4) and
1 (0.8)
if you right justify those cells, the close parentheses will be lined up. Have you tried JUST=R for those cells? At this point, you might want to open a track with Tech Support.
Cynthia
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: