Hi,
I am trying to create proc report for a site which report data monthly. We summarize the details of visits and create percentages for each month/quarter and year to date. I am struggling with calculating the percentages at each step.
I need the percentage at each report line, for each quarter and then ytd. Following is the code I am using. It has sample data.
proc format;
value quarter
1 = "Jan-Mar"
2 = "Apr-Jun"
3 = "Jul-Sep"
4 = "Oct-Dec";
run;
data have;
input year month$ quarter site_name$ status$ total_visits appointment unexpected oth date date9.;
format date date9.;
datalines;
2021 Jan 1 ABC Submit 15 6 3 0 01JAN2021
2021 Feb 1 ABC Submit 7 3 1 5 01FEB2021
2021 Mar 1 ABC Submit 11 6 3 0 01MAR2021
2021 Apr 2 ABC Submit 21 5 4 0 01APR2021
2021 May 2 ABC Submit 8 5 2 4 01MAY2021
2021 Jun 2 ABC Submit 10 7 3 0 01JUN2021
;
run;
proc report data=have;
column
("Year" year)
("Month" month)
(quarter)
("Status" status)
("Total visits"
("visits*" total_visits)
("appointment based"
("Count" appointment)
("%" pct_visits)
)
("Unexpected visits"
("Count" unexpected)
("%" pct_visits_unexpected)
)
)
("Other~" oth);
define year / order "" order=data style=[cellwidth=18mm];
define month / "" order=data style=[cellwidth=18mm];
define quarter / order noprint format=comma8.;
define Status / "" style=[cellwidth=20mm];
define total_visits / "" style=[cellwidth=25mm] format=comma8.;
define appointment / "" style=[cellwidth=25mm] format=comma8.;
define pct_visits / computed "" style=[cellwidth=25mm] format=5.2;
define unexpected / "" style=[cellwidth=25mm] format=comma8.;
define pct_visits_unexpected / computed "" style=[cellwidth=25mm] format=5.2;
define oth / "" style=[cellwidth=25mm] format=comma8.;
/*
compute before quarter;
holdtot = total_visits.sum;
app = appointment.sum;
unx = unexpected.sum;
endcomp;
compute pct_visits;
pct_visits= (app/holdtot)*100;
endcomp;
compute pct_visits_unexpected;
pct_visits_unexpected= (unx/holdtot)*100;
endcomp;
*/
compute pct_visits;
pct_visits= (appointment.sum/total_visits.sum)*100;
endcomp;
compute pct_visits_unexpected;
pct_visits_unexpected= (unexpected.sum/total_visits.sum)*100;
endcomp;
compute year;
if year ~= "" then keep = year;
if year = "" then year = keep;
endcomp;
compute Status;
if Status = "Draft" then call define (_row_, "style", "style = [backgroundcolor=pink]");
endcomp;
break after quarter/summarize;
compute after quarter;
month = put(quarter, quarter.);
call define (_row_, "style", "style = [backgroundcolor=lightorange]");
endcomp;
break after year/summarize;
compute after year;
month = "YTD";
call define (_row_, "style", "style = [backgroundcolor=lightgray]");
endcomp;
run;
Following is the output I am getting:
As you can see, it is calculating same %age for each row.
Output I want is below:
Appreciate any help!
Thank you.
... View more
