SAS Support Communities
Help
cancel
Turn on suggestions Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
since ‎11-29-2021
‎04-24-2023
ikoba
Calcite | Level 5
There are no new messages in this topic

Re: Recalculate summary data

by Calcite | Level 5 in ODS and Base Reporting
‎04-24-2023 08:07 AM
‎04-24-2023 08:07 AM
Thank you very much! ... View more
There are no new messages in this topic
Solved!

Recalculate summary data

Message contains an attachment
by Calcite | Level 5 in ODS and Base Reporting
‎04-24-2023 06:48 AM
‎04-24-2023 06:48 AM
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! ... View more
There are no new messages in this topic

Re: PROC REPORT coloring a field based on 2 other fields

by Calcite | Level 5 in ODS and Base Reporting
‎12-02-2021 08:14 AM
‎12-02-2021 08:14 AM
Hi Cynthia, Sorry, next time I will add test data and screenshot. The problem was that I missed the .MEAN. I tried with .SUM, but not with .MEAN. Thank you so much! ... View more
There are no new messages in this topic
Solved!

PROC REPORT coloring a field based on 2 other fields

by Calcite | Level 5 in ODS and Base Reporting
‎11-29-2021 05:42 PM
‎11-29-2021 05:42 PM
Hi, I have a dataset with 5 fields. study, site, subject, total_q, closed_q I would like to color the closed_q field based on closed_q/total_q. I tried this, but it is not works.   proc report data = sr.drs nowd; columns study site subject total_q closed_q ; define study/group ; define site/group ; define total_q / analysis style(header)=[background=olive]; define closed_q / analysis style(header)=[background=olive]; compute closed_q; if round((closed_q/total_q)*100,0.01)<50 then call define (_col_,"style","STYLE=[BACKGROUND=RED]"); else if round((closed_q/total_q)*100,0.01)>=50 and round((closed_q/total_q)*100,0.01)<75 then call define (_col_,"style","STYLE=[BACKGROUND=ROSE]"); else if round((closed_q/total_q)*100,0.01)>=75 and round((closed_q/total_q)*100,0.01)<85 then call define (_col_,"style","STYLE=[BACKGROUND=ORANGE]"); else if round((closed_q/total_q)*100,0.01)>=85 and round((closed_q/total_q)*100,0.01)<95 then call define (_col_,"style","STYLE=[BACKGROUND=YELLOW]"); else if round((closed_q/total_q)*100,0.01)>=95 then call define (_col_,"style","STYLE=[BACKGROUND=GREEN]"); endcomp; break after site / skip summarize style=[background=CXF2F2F2 font_weight=bold]; break after study / skip summarize style=[background=CXFFFFCC font_weight=bold]; run; I tried another solution, I added one more field (pq) to the dataset with the value of closed_q/total_q: proc sql;  update sr.drs set pq=round((closed_q/total_q)*100,0.01); quit; proc report data = sr.drs nowd; columns study site subject pq total_q closed_q ; define study/group ; define site/group ; define pq / analysis mean noprint; define total_q / analysis style(header)=[background=olive]; define closed_q / analysis style(header)=[background=olive]; compute closed_q; if pq<50 then call define (_col_,"style","STYLE=[BACKGROUND=RED]"); else if pq>=50 and pq<75 then call define (_col_,"style","STYLE=[BACKGROUND=ROSE]"); else if pq>=75 and pq<85 then call define (_col_,"style","STYLE=[BACKGROUND=ORANGE]"); else if pq>=85 and pq<95 then call define (_col_,"style","STYLE=[BACKGROUND=YELLOW]"); else if pq>=95 then call define (_col_,"style","STYLE=[BACKGROUND=GREEN]"); endcomp; break after site / skip summarize style=[background=CXF2F2F2 font_weight=bold]; break after study / skip summarize style=[background=CXFFFFCC font_weight=bold]; run; I have the same issue, closed_q column is always red. Could you please help me what is wrong here? Thank you in advance! ... View more
Latest Tags
No tags yet