Help using Base SAS procedures

Computing a second row of statistics with PROC REPORT

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Computing a second row of statistics with PROC REPORT

I’ve spent an embarrassing amount of time trying to modify this PROC REPORT code (that was automatically generated in Enterprise Guide ver. 7.1.1) to create a second row of statistics about a dataset. I need it to also compute a row for the “percent of total” like the attached screenshot does (I’m migrating our existing reports into SAS from an old Oracle product). Here is the code I’m working from and I have attached the dataset I’m using. Any help would be greatly appreciated.

  

proc report data=WORK.COUNTS_BY_WEEK nowd;
	column WEEKOF cv1 WEEKNUMBER cv2 APPROVED DENIED DEFICIENT TOTALAPPDETERM;
	define WEEKOF / group format=MMDDYYS10. missing noprint order=internal;
	define cv1 / computed 'Week of:' missing;
	compute cv1 / char length=30;
		if _break_ eq ' ' then do;
		if WEEKOF ne . then hold1=WEEKOF;
			cv1=put(hold1,MMDDYYS10.);
		end;
		if _break_ ne ' ' then cv1=' ';
	endcomp;
	define WEEKNUMBER / group missing noprint;
	define cv2 / computed 'Week #' missing;
	compute cv2 / char;
		if _break_ eq ' ' then do;
		if WEEKNUMBER ne . then hold2=WEEKNUMBER;
			cv2=put(hold2,8.);
		end;
		if _break_ ne ' ' then cv2=' ';
	endcomp;
	define APPROVED / analysis SUM 'Approved' format=COMMA9. missing;
	define DENIED / analysis SUM 'Denied' format=COMMA9. missing;
	define DEFICIENT / analysis SUM 'Deficient' format=COMMA9. missing;
	define TOTALAPPDETERM / analysis SUM 'Total Application Determinations' format=COMMA9. missing;
	rbreak after / summarize;
	run;
quit;

 

As an aside, I’d also like to thank everyone who contributes to the SAS Communities pages, you literally make it possible to do my job and I have relied on it daily for several years!


Old_report.PNG
Attachment

Accepted Solutions
Solution
‎01-30-2017 02:36 PM
New Contributor
Posts: 2

Re: Computing a second row of statistics with PROC REPORT

Posted in reply to natechamplin

Huge thanks to Jane Eslinger from SAS Technical Support for solving my problem with the following code:

proc report data=COUNTS_BY_WEEK nowd style(summary)=[font_weight=bold];
	column dummy WEEKOF cv1 WEEKNUMBER cv2 APPROVED DENIED DEFICIENT TOTALAPPDETERM;
	define dummy / group noprint;
	define WEEKOF / group format=MMDDYYS10. missing noprint order=internal;
	define cv1 / computed 'Week of:' missing;
	compute cv1 / char length=30;
		if _break_ eq ' ' then do;
		if WEEKOF ne . then hold1=WEEKOF;
			cv1=put(hold1,MMDDYYS10.);
		end;
		if _break_ ne ' ' then cv1=' ';
	endcomp;
	define WEEKNUMBER / group missing noprint;
	define cv2 / computed 'Week #' missing;
	compute cv2 / char;
		if _break_ eq ' ' then do;
		if WEEKNUMBER ne . then hold2=WEEKNUMBER;
			cv2=put(hold2,8.);
		end;
		if _break_ = '_RBREAK_' then cv2='Percent';
		if upcase(_break_)='DUMMY' then cv2='Total';
	endcomp;
	define APPROVED / analysis SUM 'Approved' format=COMMA9. missing;
	define DENIED / analysis SUM 'Denied' format=COMMA9. missing;
	define DEFICIENT / analysis SUM 'Deficient' format=COMMA9. missing;
	define TOTALAPPDETERM / analysis SUM 'Total Application Determinations' format=COMMA9. missing;
	rbreak after / summarize; *this now gives the percent row;
	break after dummy / summarize; *this gives the total row;

	compute after; *this will change the percent row to have the values you need;
		approved.sum = approved.sum / TOTALAPPDETERM.sum;
		denied.sum = denied.sum / TOTALAPPDETERM.sum;
		deficient.sum = deficient.sum / TOTALAPPDETERM.sum;
		TOTALAPPDETERM.sum = TOTALAPPDETERM.sum / TOTALAPPDETERM.sum;

		*change formatting;
		call define('approved.sum','format','percent8.1');
		call define('denied.sum','format','percent8.1');
		call define('deficient.sum','format','percent8.1');
		call define('TOTALAPPDETERM.sum','format','percent8.1');
	endcomp;
	run;
quit;

Hopefully this can help someone else as well.

View solution in original post


All Replies
Solution
‎01-30-2017 02:36 PM
New Contributor
Posts: 2

Re: Computing a second row of statistics with PROC REPORT

Posted in reply to natechamplin

Huge thanks to Jane Eslinger from SAS Technical Support for solving my problem with the following code:

proc report data=COUNTS_BY_WEEK nowd style(summary)=[font_weight=bold];
	column dummy WEEKOF cv1 WEEKNUMBER cv2 APPROVED DENIED DEFICIENT TOTALAPPDETERM;
	define dummy / group noprint;
	define WEEKOF / group format=MMDDYYS10. missing noprint order=internal;
	define cv1 / computed 'Week of:' missing;
	compute cv1 / char length=30;
		if _break_ eq ' ' then do;
		if WEEKOF ne . then hold1=WEEKOF;
			cv1=put(hold1,MMDDYYS10.);
		end;
		if _break_ ne ' ' then cv1=' ';
	endcomp;
	define WEEKNUMBER / group missing noprint;
	define cv2 / computed 'Week #' missing;
	compute cv2 / char;
		if _break_ eq ' ' then do;
		if WEEKNUMBER ne . then hold2=WEEKNUMBER;
			cv2=put(hold2,8.);
		end;
		if _break_ = '_RBREAK_' then cv2='Percent';
		if upcase(_break_)='DUMMY' then cv2='Total';
	endcomp;
	define APPROVED / analysis SUM 'Approved' format=COMMA9. missing;
	define DENIED / analysis SUM 'Denied' format=COMMA9. missing;
	define DEFICIENT / analysis SUM 'Deficient' format=COMMA9. missing;
	define TOTALAPPDETERM / analysis SUM 'Total Application Determinations' format=COMMA9. missing;
	rbreak after / summarize; *this now gives the percent row;
	break after dummy / summarize; *this gives the total row;

	compute after; *this will change the percent row to have the values you need;
		approved.sum = approved.sum / TOTALAPPDETERM.sum;
		denied.sum = denied.sum / TOTALAPPDETERM.sum;
		deficient.sum = deficient.sum / TOTALAPPDETERM.sum;
		TOTALAPPDETERM.sum = TOTALAPPDETERM.sum / TOTALAPPDETERM.sum;

		*change formatting;
		call define('approved.sum','format','percent8.1');
		call define('denied.sum','format','percent8.1');
		call define('deficient.sum','format','percent8.1');
		call define('TOTALAPPDETERM.sum','format','percent8.1');
	endcomp;
	run;
quit;

Hopefully this can help someone else as well.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 164 views
  • 1 like
  • 1 in conversation