BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
natechamplin
Fluorite | Level 6

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
1 ACCEPTED SOLUTION

Accepted Solutions
natechamplin
Fluorite | Level 6

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

1 REPLY 1
natechamplin
Fluorite | Level 6

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.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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