Decimal formatting Issues in the Macro

[SASuserlot]

 I found a macro for decimal aligning/ controlling of  statistical table values that comes from Means Procedure. Unfortunately I am having trouble with  format in the  values column ( 'Stat' variable) for compound stats . Compound stats are those where you display the stats in combinations like 'Mean (SD) or Min-Max" for some reason these values are not displaying properly   Where as individual stats like 'Median displayed' correctly. I believe Macro Parameter  'unit_place=' and 'fmt2' in the macro is the culprit but I really could not understand how to fix it and guide me how to use it properly . If I increase the number its throwing me the WD format in the log. Please pinpoint or correct the macro where I am doing it wrong. Thanks.

I expected macro display Mean 1 more digit and  SD 2 more digits after the decimal. but SD having the problem here and same applies to the 'MaX'

/****************************************************************************
****************************/
/* Macro align_stat
*/
/*
*/
/* This macro serves the purpose of dynamically generating a format in order
to easily align */
/* descriptive statistics. You can also change the alignment of the
statistics by increasing or */
/* decreasing the value of the parameter UNIT_PLACE.
*/
/*
*/
/* Considerarions before use: In order for it to work correctly, it must be
invoked within a data step */
/* where the necessary variable(s) exist, said variables should contain the
descriptive statistics to */
/* be aligned, and a variable containing the maximum number of decimals for
the values before */
/* summarizing.
*/
/*
*/
/* For compound statistics, by default the separator is aligned to the
decimal point of single sta- */
/* tistics (provided you specify the same value to the parameter UNIT_PLACE
of all statistics.) */
/*
*/
/* Parameters:
*/
/* - UNIT_PLACE: The place at which the unit of the statistic will be
aligned to for single */
/* statistics.
*/
/* For composite statistics, if the same number is
specified, the separator will be */
/* aligned to the decimal point. (REQUIRED, DEFAULT is 8)
*/
/* - MAXDEC_VAR: This is the name of the data set variable containing the
maximum number of decimals */
/* per group/data set. (REQUIRED)
*/
/* - EXTRA_DECN: It is comoon that SAP specifies that some statistics
should have 1 or 2 more deci- */
/* mal palces than the maximum number of decimal places
found in the values being */
/* summarized. for single statistics specify just one value,
for compsite statistics */
/* speicify one value if you want it to be used for both
statistics, or one value for */

/* each statistic. (DEFAULT is 0).
*/
/* - COMP_SEP: Specify the separator for composite statistics. Valid
values are COMMA, HYPHEN and */
/* PARENTHESIS (either lowecase, upcase or any combination).
(Required only for compo- */
/* statistics).
*/
/* - VAR: Specify the data set varible(s) that will supply the
values for the statistics. */
/* Specify 2 varibles separated by a space for composite
statistics. (REQUIRED) */
/* - NEW_VAR: Specify the name of a new varible to store
*/
/* - NC_DISP: Especify a value to be displayed when a value cannot be
calculated (i.e. SD when */
/* when there is only one value. In single statistics, this
value is aligned to the */
/* decimal point of other single statistics. In compound
statistics, the alignment is */
/* dependent on the kind of separator used. IMPORTAN: Blank
spaces are not a valid */
/* value. Instead specify the word _BLANK_ if you dont want
a label to be displayed. */
/* For compound statistics specify two values separated by
an space, of if you spe- */
/* cify only one value, it will be used for both statistics.
*/
/*
*/
/****************************************************************************
****************************/
%macro align_stat( unit_place=, maxdec_var=, extra_decn=, comp_sep=, var=,
new_var=, NC_DISP=_BLANK_);
 %let bv=&unit_place.;
 %let w1=WAR;
 %let w2=NING;
 %let no1=NO;
 %let no2=TE;
 %* Parameter integrity checks (in order to make sure the parameters have
correct values, and to display a note or warning to the user
 or terminate the macro execution if needed);
 %if %sysfunc(countw(&unit_place)) ne 1 %then %do;
 %put &no.&no2.: More numbers than required or parameter UNIT_PLACE
missing.;
%goto exit;
 %end;
 %if &maxdec_var= %then %do;
 %put &w1.&w2.: Parameter MAXDEC_VAR required.;
 %goto exit;

 %end;
 %if &comp_sep ne %then %do;
 %if %upcase(&comp_sep) ne COMMA and %upcase(&comp_sep) ne PARENTHESIS and
%upcase(&comp_sep) ne HYPHEN %then %do;
 %put &w1.&w2.: Invalid value for parameter COMP_SEP;
 %goto exit;
%end;
%if %sysfunc(countw(&extra_decn)) = 1 %then %do; %put &no1.&no2.: The
resulting value will be comprised of 2 statistis.;
 %put &no1.&no2.- Only
one value for extradigits found.;

%put &no1.&no2.- It will be used for both statistics.;
 %end;
%else %if %sysfunc(countw(&extra_decn)) ne 2 or &extra_decn= %then
%do;
 %put &w1.&w2.: Invalid or missing value for parameter EXTRA_DECN;
 %goto exit;
%end;
%if %sysfunc(countw(&NC_DISP)) = 1 %then %do; %put &no1.&no2.: The
resulting value will be comprised of 2 statistis.;
 %put &no1.&no2.- Only
one value for NC_DISP found.;

%put &no1.&no2.- It will be used for both statistics.;
 %end;
%else %if %sysfunc(countw(&NC_DISP)) ne 2 or &NC_DISP= %then %do;
 %put &w1.&w2.: Invalid or missing value for parameter NC_DISP;
 %goto exit;
%end;
%if %sysfunc(countw(&var.)) ne 2 or &var.= %then %do;
 %put &w1.&w2.: Invalid or missing value for parameter VAR.;
 %put &w1.&w2.- Two variables must be especified for composite
statistics.;
 %goto exit;
%end;
 %end;
 %if &new_var= %then %do;
 %put &w1.&w2.: The name of a new character variable to store the aligned
value must be specified;
%goto exit;
 %end;
 %* check if the varible that will store the new statistic exists;
 call missing(&new_var);
 if vtype(&new_var)='N' then do;
 put "WAR" "NING: Either the variable &NEW_VAR. does not exist or its type
is numeric.";
 put "Please create it as a character variable before invoking this
macro";

 end;
 %* assign values from parameters to local macro variables for correct
execution of the macro ;
 %* local macro descriptions below: ;
 %* - UP1 and UP2: these varibles will store the same value as parameter
UNIT_PLACE separately in order to
 avoid confusion as to what parts of code are being used for each
statistic in compound values.;
 %* - ED1 and ED2: The same reasoning as UP1 and UP2 apply to these local
macro variables. In addition, if parameter EXTRA_DECN
 is assigned a single value, the same value will be used for both ED1
and ED2, if EXTRA_DECN is assigned 2 values, the first
 value will be assigned to ED1 and the second to ED3.;
 %* - NCD1 and NCD2: The same reasoning as UP1 and UP2 apply to these local
macro variables. In addition, if parameter NC_DISP
 is assigned a single value, the same value will be used for both NCD1
and NCD2, if NC_DISP is assigned 2 values, the first
 value will be assigned to NCD1 and the second to NCD3. If a no value
is to be displayed when a statistic cannot be computed
 then _blank_ should be specified (not case sensitive);
 %if &comp_sep ne %str() %then %do;
 %if %sysfunc(countw(&unit_place.))=1 %then %do; %let up1=&unit_place.;
%let up2=&unit_place; %end;
%if %sysfunc(countw(&extra_decn.))=1 %then %do; %let ed1=&extra_decn.;
%let ed2=&extra_decn; %end;
%else %if %sysfunc(countw(&extra_decn.))=2 %then %do; %let
ed1=%scan(&extra_decn.,1); %let ed2=%scan(&extra_decn,2); %end;
%if %sysfunc(countw(&NC_DISP.))=1 %then %do;
 %if %index(%upcase(&NC_DISP.),_BLANK_) %then %do; %let ncd1=; %let
ncd2=; %end;
 %else %do; %let ncd1=&NC_DISP.; %let ncd2=&NC_DISP; %end;
%end;
%else %if %sysfunc(countw(&NC_DISP.))=2 %then %do iii=1 %to 2;
 %if %scan(%upcase(&NC_DISP.),&iii.)=_BLANK_ %then %let ncd&iii.=;
 %else %let ncd&iii.=%scan(&NC_DISP.,&iii.);
%end;
 %* Align value for non calculable stats;
%* The alignment of the value for non calculable stats is done as
follows:
 - Create local macro variable NC_VAL.
 - Subtract the length of NCD1 to the number in UNIT_PLACE
parameter+1 and add to the NC_VAL the same number of spaces as the
 resulting number from the subtraction, and then add the value of
NCD1.(Please note that if the same number is specified for UNIT_PLACE
 for all statistics, then the last character of the value in NCD1
will be aligned to the decimal point of sngle statistic values and to
 the separator in composite values.) ;
 %let NC_VAL=;
 %let UPL=%eval(&unit_place+1 - %length(&ncd1.));
 %do iii=1 %to &UPL;

 %if &iii < &upl. %then %let NC_VAL=&NC_VAL.%str( );
 %else %let NC_VAL=&NC_VAL.%str( )&ncd1.;
 %end;
%* Local macro variables VAR1 and VAR2
 - For single-statistic values, VAR1 will contain the variable
specified in the parameter VAR. If parameter VAR contains
 the name of 2 variables, then VAR1 will be assigned the first and
VAR2 will be assigned the second.;
%if %sysfunc(countw(&var.))=2 %then %do; %let var1=%scan(&var.,1); %let
var2=%scan(&var,2); %end;
%else %if %sysfunc(countw(&var.))=1 %then %do; %let var1=&var; %let
var2=; %end;
%* Create local macro variables OP and CL for composite-statistic
values. Their value will change as depending on the value of
 the parameter COMP_SEP.;
%* Create DATA SET variables RND1 and RND2 that will contain the number
to be used within the ROUND function.;
%* Create DATA SET variables FMT1 and FMT2 that will contain the
formats to be applied to each statistic.;

 fmt1=' ';
 fmt2=' ';
 %if %upcase(&comp_sep)=PARENTHESIS %then %do;
 %let op=%str( %();
 %let cl=%str(%));
 rnd1=10**-(&maxdec_var.+&ed1.);
 fmt1=strip(put(&up1.-
1,2.))||"."||strip(put(&maxdec_var.+&ed1.,10.))||"-r";
 rnd2=10**-(&maxdec_var.+&ed2.);

fmt2=strip(put(&up2.,2.))||"."||strip(put(&maxdec_var.+&ed2.,10.))||"-r";
 %end;
 %else %if %upcase(&comp_sep)=HYPHEN %then %do;
 %let op=%str( - );
 %let cl=;
 rnd1=10**-(&maxdec_var.+&ed1.);
 fmt1=strip(put(&up1.-
1,2.))||"."||strip(put(&maxdec_var.+&ed1.,10.))||"-r";
 rnd2=10**-(&maxdec_var.+&ed2.);

fmt2=strip(put(&up2.+1,2.))||"."||strip(put(&maxdec_var.+&ed2.,10.))||"-r";
%end;
 %else %if %upcase(&comp_sep)=COMMA %then %do;
 %let op=%str(, );
 %let cl=;
 rnd1=10**-(&maxdec_var.+&ed1.);

fmt1=strip(put(&up1.,2.))||"."||strip(put(&maxdec_var.+&ed1.,10.))||"-r";
 rnd2=10**-(&maxdec_var.+&ed2.);

fmt2=strip(put(&up2.+1,2.))||"."||strip(put(&maxdec_var.+&ed2.,10.))||"-r";
 %end;

 if ^missing(&var1.) and missing(&var2.) then
&new_var=putn(round(&var1.,rnd1),fmt1)||"&op."||strip("&ncd2.")||"&cl.";
 else if cmiss(&var1.,&var2.)=2 then &new_var.="&nc_val.";
 else if cmiss(&var1.,&var2.)=0 then
&new_var=putn(round(&var1.,rnd1),fmt1)||"&op."||strip(putn(round(&var2.,rnd2)
,fmt2))||"&cl.";
 %end;
 %* For single statistic values we follow a similar procedure as for
composite statistics values, doing the appopiate chages ;
 %else %do;
 %let up1=&unit_place.;
%let ed1=&extra_decn.;
%* Align value for missing or non calculable stats;
%if %index(%upcase(&NC_DISP.),_BLANK_) %then %let ncd1=;
%else %let ncd1=&NC_DISP.;
%let NC_VAL=;
 %let UPL=%eval(&unit_place+1 - %length(&ncd1.));
 %do iii=1 %to &UPL;
 %if &iii < &upl. %then %let NC_VAL=&NC_VAL.%str( );
 %else %let NC_VAL=&NC_VAL.%str( )&ncd1.;
 %end;
rnd1=10**-(&maxdec_var.+&ed1.);
fmt1=strip(put(&up1.+(&maxdec_var.>0 or
&ed1.>0)+&maxdec_var.+&ed1.,2.))||"."||strip(put(&maxdec_var.+&ed1.,10.))||"-
r";
rnd2=.;
fmt2='';
 if missing(&var.) then &new_var.="&NC_VAL.";
else &new_var=putn(round(&var.,rnd1),fmt1);
 %end;
%exit: 
%mend align_stat;

proc means data= sashelp.class noprint  nway n median mean min max std;
class sex;
var weight;
output out= xx n = n
				Mean = mean
				Median = median
				Min = min
				Max = max 
				std =sd;
run;
/**/
/*proc transpose data = xx out= xy;*/
/*by sex;*/
/*id _STAT_;*/
/*var weight;*/
/*run;*/

data dec0;
set sashelp.class;
mip =scan(strip(put(weight,best.)),2,'.');
	if not missing(mip)then dcm = lengthc(strip(mip));
	else dcm = 0;
run;

Proc Sql ;
create table Dec1 as select distinct sex,max(dcm)as decx from dec0
group by sex;
quit;

data xy_o;
merge xx dec1;
by sex;
run;


data xy1;
set xy_o;
/*if sex = '' then maxdec = 1;*/
/*if sex = 'F' then maxdec = 0;*/
/*if sex ='M' then maxdec =0;*/
/*mc = put(mean,best.);*/
/*sc = put(sd,best.);*/
maxdec=decx;
drop decx _:
run;

options mprint mlogic symbolgen;
data xy2;
length statc  stat$200;
set xy1;
statc= 'Median';
%align_stat( unit_place=8, maxdec_var=maxdec, extra_decn=1 , comp_sep=, var=median,
new_var=stat, NC_DISP=_BLANK_);
output;
statc= 'Mean (SD)';
%align_stat( unit_place=8, maxdec_var=maxdec, extra_decn=1 2 , comp_sep= parenthesis, var=mean sd,
new_var=stat, NC_DISP=_BLANK_ NA);
output;
statc= 'Min-Max';
%align_stat( unit_place=8, maxdec_var=maxdec, extra_decn=0 0 , comp_sep= HYPHEN, var=min max,
new_var=stat, NC_DISP=_BLANK_);
output;

run;

Macro / Article Resourcd: https://www.lexjansen.com/pharmasug/2017/PO/PharmaSUG-2017-PO20.pdf 

[FreelanceReinh · Accepted solution]

Hello @SASuserlot,

I see that you are already using a LENGTH statement for variables statc and stat. For a quick fix, I suggest that you insert fmt1 and, most importantly, fmt2 into this LENGTH statement so that the values of fmt2 are no longer truncated (which causes the issue).

length statc stat fmt1 fmt2 $200;

[SASuserlot]

Thanks @FreelanceReinh . You are a Magician😊, it Worked.  Just out of curiosity.  is there any alternative way , where it just comes from macro, instead of quick fix.  What I am doing wrong in my 'xy2'  dataset step ? I expected it should be adjusted from the macro as per the article . I guess I messed up some thing. Thanks again for your time.

[FreelanceReinh]

---

@SASuserlot wrote:

Thanks @FreelanceReinh . You are a Magician😊, it Worked.  Just out of curiosity.  is there any alternative way , where it just comes from macro, instead of quick fix.  What I am doing wrong in my 'xy2'  dataset step ? I expected it should be adjusted from the macro as per the article . I guess I messed up some thing. Thanks again for your time.

---

You're welcome. No, I'm a mathematician.

I haven't read the article yet, only the name of the author. Will have a closer look over the weekend (which is going to start in a few minutes in my time zone).

[FreelanceReinh]

Regarding the issue with the truncated FMT2 values in the macro:

1. Copying code from a PDF document is always error-prone. In your case the original strings of length 7 (seven blanks) used to initialize variables FMT1 and FMT2,
   

    fmt1='       ';
    fmt2='       ';

   were compressed (in the copy process) to
   

    fmt1=' ';
    fmt2=' ';

   which would have affected all statistics in your data step had the first statistic been a composite statistic. Starting with a single statistic (here: median) you were half lucky: The initialization above is only part of a conditional %DO-%END block in the macro (which is hard to see without proper indentation) and the %IF condition is &comp_sep ne %str(), so the code above didn't apply in your first macro call and the length of FMT1 was determined by a later assignment statement with a sufficiently long, non-blank value.
2. However, FMT2 was initialized by the statement
   

    fmt2='';

   close to the end of the macro. It almost seems to me that the author didn't realize that the implied length specification (length 1) of this assignment statement -- in a %DO-%END block for single statistics only -- would still be effective when composite statistics were processed in a later macro call in the same data step after the first macro call processed a single statistic. A possible correction is, of course, to write
   

    fmt2='       ';

   (with seven blanks, which are sufficient) as in the earlier initialization.

There is another minor mistake early in the macro code: The macro variable reference &no. in the very first %PUT statement should read &no1. to be consistent with the definition a few lines earlier.

As to your own code:

There's a minor mistake in the data step creating dataset XY1: a missing semicolon after the DROP statement.

---

@SASuserlot wrote:

What I am doing wrong in my 'xy2'  dataset step ? I expected it should be adjusted from the macro as per the article . I guess I messed up some thing.

---

I'm not sure what you mean. Once the truncation issue with FMT2 is resolved, I think the statistics are aligned as described in the article. In particular, for "values comprised of 2 statistics, the separator will be aligned to the decimal point of single-statistic values" (page 4).

stat

     90.00
 90.11 (19.384)
  50.5 - 112.5
    107.25
108.95 (22.727)
  83.0 - 150.0

Would you prefer the first part of a composite statistic to be aligned with the single statistics?

stat

 90.00
 90.11 (19.384)
 50.5 - 112.5
107.25
108.95 (22.727)
 83.0 - 150.0

For this I think you can define the FMT1 values in the three %DO-%END blocks regarding composite statistics (for parenthesis, hyphen and comma, respectively) in a similar way as for single statistics, i.e.

fmt1=strip(put(&up1.+(&maxdec_var.>0 or &ed1.>0)+&maxdec_var.+&ed1.,2.))||...

This worked for your example, but you would need to test it more thoroughly (e.g., with cases where some statistics cannot be calculated, etc.).

[SASuserlot]

Thank you @FreelanceReinh . I really appreciate you taking time and  pointing out the issues and giving solution. As per now it will do the job I required. Yes I will definitely try  the different scenarios.