Help using Base SAS procedures

SAS PROC Report Totals on Second Column

Accepted Solution Solved
Reply
Super Contributor
Posts: 339
Accepted Solution

SAS PROC Report Totals on Second Column

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? 

 

GroupUserJan FebMarAprMayJunTotal
Group 1A1243823769171
 B326586121243250
 C1234231261299
 TOTAL56142117479464520
Group 2A0000000
 B1100011
 TOTAL1100011

Accepted Solutions
Solution
‎03-22-2018 05:41 AM
SAS Super FREQ
Posts: 9,373

Re: SAS PROC Report Totals on Second Column

[ Edited ]

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:
total_col_2.png

 

  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

View solution in original post


All Replies
Respected Advisor
Posts: 3,066

Re: SAS PROC Report Totals on Second Column

[ Edited ]

@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?

--
Paige Miller
SAS Super FREQ
Posts: 9,373

Re: SAS PROC Report Totals on Second Column

Posted in reply to PaigeMiller
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
Super Contributor
Posts: 339

Re: SAS PROC Report Totals on Second Column

[ Edited ]
Posted in reply to Cynthia_sas

 

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):

 

GroupUserJan FebMarAprMayJunTotal
Group 1A1243823769171
 B326586121243250
 C1234231261299
Group 1 56142117479464520
Group 2A0000000
 B1100011
Group 2  1100011

 

Desired:

 

GroupUserJan FebMarAprMayJunTotal
Group 1A1243823769171
 B326586121243250
 C1234231261299
 TOTAL56142117479464520
Group 2A0000000
 B1100011
 TOTAL1100011
Solution
‎03-22-2018 05:41 AM
SAS Super FREQ
Posts: 9,373

Re: SAS PROC Report Totals on Second Column

[ Edited ]

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:
total_col_2.png

 

  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

Super Contributor
Posts: 339

Re: SAS PROC Report Totals on Second Column

Posted in reply to Cynthia_sas

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;
☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 181 views
  • 0 likes
  • 3 in conversation