topic Re: Proc report Compute Percentage after each quarter and Year End in SAS Programming

Proc report Compute Percentage after each quarter and Year End

Rydhm — Wed, 01 Sep 2021 16:17:32 GMT

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.

Re: Proc report Compute Percentage after each quarter and Year End

ballardw — Wed, 01 Sep 2021 01:27:02 GMT

First thing:

compute after quarter;
	month = put(quarter, quarter.);
	call define (_row_, "style", "style = [backgroundcolor=lightorange]");
endcomp;

Your quarter values are numeric 1 to 4 and 1) there is no "quarter" format, 2) the QTR. format is for dates. So what do you expect to display when Quarter=1? Unless you have written a custom format that you did not share perhaps????

Compute before what in the next block? As written this calculates the same thing before every line. Did you check and see that percentage is the overall, i.e. 32/72 for appointment and 16/72 for unexpected visits?

compute before;
    holdtot = total_visits.sum;
   app = appointment.sum;
   unx = unexpected.sum;
endcomp;

Take a look at what happens when you add quarter to the compute before in the block above. That may show you part of what is happening.

Since I don't know which percentage you want where I am stopping here.

Re: Proc report Compute Percentage after each quarter and Year End

Rydhm — Wed, 01 Sep 2021 16:05:42 GMT

Sorry, I forgot to add the format. I am using a custom format for quarter, it is edited now. Also, I have added the expected output table. It calculates the percentage at each row for Appointment based visits and Unexpected visits.

Re: Proc report Compute Percentage after each quarter and Year End

Rydhm — Wed, 01 Sep 2021 16:18:45 GMT

Never mind! It worked, thanks for your idea to play with compute block. I have updated code. Thanks .