topic Re: Recalculate summary data in ODS and Base Reporting

Recalculate summary data

ikoba — Mon, 24 Apr 2023 10:48:47 GMT

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!

Re: Recalculate summary data

PaigeMiller — Mon, 24 Apr 2023 11:27:07 GMT

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.

Re: Recalculate summary data

FreelanceReinh — Mon, 24 Apr 2023 11:57:52 GMT

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.

Re: Recalculate summary data

ikoba — Mon, 24 Apr 2023 12:07:16 GMT

Thank you very much!