topic Re: SAS PROC Report Totals on Second Column in SAS Procedures

SAS PROC Report Totals on Second Column

milts — Tue, 20 Mar 2018 04:20:48 GMT

How will I be able to generate this layout using proc report where the "TOTAL" label is on the User column and not on the Group column?

Group	User	Jan	Feb	Mar	Apr	May	Jun	Total
Group 1	A	12	43	8	23	76	9	171
	B	32	65	86	12	12	43	250
	C	12	34	23	12	6	12	99
	TOTAL	56	142	117	47	94	64	520
Group 2	A	0	0	0	0	0	0	0
	B	1	1	0	0	0	1	1
	TOTAL	1	1	0	0	0	1	1

Re: SAS PROC Report Totals on Second Column

PaigeMiller — Tue, 20 Mar 2018 15:31:21 GMT

@milts wrote:

... where the "TOTAL" label is on the User column and not on the Group column?

Please clarify this request. Are you asking how to move the label (that would be a literal reading of your words), or are you asking how to move the entire column of data plus the label (which would not be what you said above, but makes more sense to me)?

Can you show us what the report should look like?

Re: SAS PROC Report Totals on Second Column

Cynthia_sas — Tue, 20 Mar 2018 13:59:51 GMT

Also, what code have you tried? Can you post any data to test with? What destination are you interested in? and what are the lengths of the Group column and the User column respectively?

cynthia

Re: SAS PROC Report Totals on Second Column

milts — Thu, 22 Mar 2018 09:28:01 GMT

Hi,

Sorry for the ambiguity of my post. Here's the script that I currently have. If I remove the suppress on the break statement, I'll have the Group Total still on column 1. What I would like to do is to have the TOTAL label placed on column 2. I know that this can also be done in proc tabulate but the client wants it to be on proc report. I'm not sure how it can be done and greatly appreciate inputs.

proc report data=mydata;
	column group user monthend;
	define group / group 'Group';
	define user / group 'User';
	define monthend / across '';
	break after group / summarize suppress;
run;

Current (if suppress is removed):

Group	User	Jan	Feb	Mar	Apr	May	Jun	Total
Group 1	A	12	43	8	23	76	9	171
	B	32	65	86	12	12	43	250
	C	12	34	23	12	6	12	99
Group 1		56	142	117	47	94	64	520
Group 2	A	0	0	0	0	0	0	0
	B	1	1	0	0	0	1	1
Group 2		1	1	0	0	0	1	1

Desired:

Group	User	Jan	Feb	Mar	Apr	May	Jun	Total
Group 1	A	12	43	8	23	76	9	171
	B	32	65	86	12	12	43	250
	C	12	34	23	12	6	12	99
	TOTAL	56	142	117	47	94	64	520
Group 2	A	0	0	0	0	0	0	0
	B	1	1	0	0	0	1	1
	TOTAL	1	1	0	0	0	1	1

Re: SAS PROC Report Totals on Second Column

Cynthia_sas — Thu, 22 Mar 2018 03:43:35 GMT

Hi: I am not sure that your PROC REPORT code actually generated what you posted. For example, you show labels of 'app' and 'user' for two of your variables and those labels are not present in the output you posted.

You did not say what the length of the USER variable is. My guess it is a length of $1 -- if this is the case, you may have to perform a bit of "massaging" to make the word TOTAL fit.

Since you did not provide data, here's an example with 2 regions from SASHELP.SHOES -- I used PRODUCT as the ACROSS variable, I didn't have anything with a MONTHEND variable.:

proc sort data=sashelp.shoes out=shoes;
  by region subsidiary;
  where region in ('Canada', 'United States');
run;

proc report data=shoes
  style(summary)={fontweight=bold};
  column region subsidiary product n ;
  define region / group 'app';
  define subsidiary / group 'user';
  define product / across ' ';
  define n / 'Total';
  break after region / summarize suppress;
  rbreak after / summarize;
  compute after region;
    subsidiary = 'TOTAL';
  endcomp;
  compute after;
    subsidiary = 'GRAND TOTAL';
  endcomp;
run;

And this is the output created:

If the length of your USER variable is smaller than the size you need for the word "TOTAL", then you will have to use the technique shown in this posting https://communities.sas.com/t5/ODS-and-Base-Reporting/proc-report/m-p/443393#M20600 to increase the length of the USER variable before your PROC REPORT step.

Hope this gives you an idea of how to proceed.

cynthia

Re: SAS PROC Report Totals on Second Column

milts — Thu, 22 Mar 2018 09:46:10 GMT

Sorry there's a mismatch between on my initial example and the script I copied over from the server.

Anyway, many thanks for your example as I finally got the missing portion that is required on my script which is this portion.

 compute after region;
    subsidiary = 'TOTAL';
  endcomp;