Recalculate summary data

[ikoba]

Hi,

I would like to ask you about a problem:

I have a dataset with study, site, approved_terms, approval_requiring,requiring_coding, percent_terms approved

percent_terms approved was calculated as approved_terms/(approved_terms+approval_requiring+requiring_coding)

I have a summary row on site and study level. 

My problem is that I do not know how to recalculate the percent_terms approved field on summary rows accourding to the formula, as it is an average in the summery row, which is not correct.

I have attached the aoutput in excel.

The code is:

proc format;
value fcolor
0 - 75 = 'red'
75 - 95 = 'orange'
95 - 1000 = 'green';

run;

ods excel file="z:\Studies\UCAB-CT-05\DM\Output\Status\DRS\Regular\aaa_&sysdate9..xlsx" ;
ods excel options(autofilter="1-70" sheet_name = "DRS" embedded_titles='yes');

proc report data = ds.drs nowd;
columns study site subject medta medtra vrc p_medta ;
define study/group ;
define site/group ;

/*p_medta=round((medta/(medta+medtra+vrc))*100,0.01);*/
define medta / style(header)=[background=CXD9E1F2];
define medtra / style(header)=[background=CXD9E1F2];
define vrc / style(header)=[background=CXD9E1F2];
define p_medta / ANALYSIS
MEAN
FORMAT=10.2
style(header)=[background=CXD9E1F2];

compute p_medta;
call define
(_col_,'style',"STYLE=[BACKGROUND=fcolor.]");
endcomp;
break after site / skip summarize style=[background=CXF2F2F2 font_weight=bold];
break after study / skip summarize style=[background=CXFFFFCC font_weight=bold];

run;
ods excel close;

Thank you!

[PaigeMiller]

Most of us would like to help, but we cannot make use of Excel files. Many simply refuse to download Excel files as they can be security threats.

Please provide us data by following these instructions: https://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/, or type the data in as working SAS data step code yourself. We don't need the entire data set, a portion of the data will do fine, as long as the data allows us to understand the problem and code it properly.

[FreelanceReinh · Accepted solution]

Hi @ikoba,

I think you should define p_medta as a computed variable, not as an analysis variable:

define p_medta / computed format=10.2 style(header)=[background=CXD9E1F2];

Then insert this formula into the existing COMPUTE block for p_medta:

p_medta=round((medta.sum/(medta.sum+medtra.sum+vrc.sum))*100,0.01);

The compound variable names (of the form variablename.statistic) are necessary because medta, medtra and vrc are analysis variables and sum is their default statistic.

[ikoba]

Thank you very much!