DATA Step, Macro, Functions and more

PROC REPORT create a vertical dividing line between variables/ Subtotal & GrandTotal labels trimmed

Accepted Solution Solved
Reply
Contributor
Posts: 45
Accepted Solution

PROC REPORT create a vertical dividing line between variables/ Subtotal & GrandTotal labels trimmed

Dear all,

 

I am running the attached code:

 

ods html;

proc report data=sashelp.prdsale(keep=country region actual predict)

style(summary)=Header;

column country region actual predict;

define country / group;

define region /group;

define actual /analysis sum;

define predict /analysis sum;

break after country / summarize;

rbreak after /summarize;

compute after country;

 country = catx(' ',"Subtotal for Country",region);

endcomp;

compute after ;

 country = "Country GrandTotal";

endcomp;

run;

ods html close;

 

My problems are as follows:

 

1).--  I would like to create a VERTICAL,say gray coloured line, between ACTUAL and PREDICT variables.

 

2).-- I try to create a label "Subtotal for Country",after Country but it is not being created

 

3).-- the same happens to "Country GrandTotal" which is being "trimmed"

Is it possible that I can "merge" the empty box of region in order to span the lengthy labels?

 

Any hints / suggestions would be more than welcome.

 

Thanking you in advance.

 

Best regards


Accepted Solutions
Solution
‎07-17-2017 04:54 PM
Frequent Contributor
Posts: 89

Re: PROC REPORT create a vertical dividing line between variables/ Subtotal & GrandTotal labels

Posted in reply to Zeus_Olympous

I'm going to answer in a different order than your questions.

 

I ran your example in SAS University Edition.  There are warnings and notes in the log:

 

 WARNING: In a call to the CATX function, the buffer allocated for the result was not long enough to contain the concatenation of 
          all the arguments. The correct result would contain 20 characters, but the actual result might either be truncated to 10 
          character(s) or be completely blank, depending on the calling environment. The following note indicates the left-most 
          argument that caused truncation.
 NOTE: Argument 2 to function CATX(' ','Subtotal for'[12 of 20 characters shown],'          ') at line 1 column 10 is invalid.

The warnings and notes are for the compute after country block.  One issue is that the country variable has a length of 10, and the text you want to use is longer than 10 characters.  Another issue is that you are trying to concatenate the region variable.  The compute after country block only knows the value of country.

 

To use longer text in the country column, you need to adjust the length of the variable.

 

Suggested code:

 

proc sql;
  create table prdsale as
    select *
      from sashelp.prdsale;
     
  alter table prdsale
    modify country char(20) format=$20. ;
quit;

  
proc report data=prdsale(keep=country region actual predict) 
		style(summary)=Header spanrows
		style(column)={borderwidth=2}
		style(header)={borderwidth=2};
	column country region actual predict;
	define country / group;
	define region /group;
	define actual /analysis sum style(column)={borderrightcolor=red}
	                            style(header)={borderrightcolor=red};
	define predict /analysis sum;
	break after country / summarize;
	rbreak after /summarize;
    compute country;
      if upcase(_break_) = 'COUNTRY' then 
        do;
          country = catx(' ', country, 'Total');
        end;
      else if _break_ in ('_RBREAK_') then 
        do;
          country='Grand Total';
        end;
    endcomp;
run;

The borders in the output when using the HTMLBlue style in SAS University Edition are gray, so I chose red for the border between actual sales and predicted sales.

 

Output from suggested code:

 

PROC REPORT with red vertical bar between actual and predicted sales.jpg 

 

Two references:

 

The REPORT Procedure: A Primer for the Compute Block -- Jane Eslinger's paper at SAS Global Forum 2015.

 

Go Ahead and _BREAK_-down: Advanced COMPUTE Block Examples -- Cynthia Zender's paper at SAS Global Forum 2017.

 

 

View solution in original post


All Replies
Solution
‎07-17-2017 04:54 PM
Frequent Contributor
Posts: 89

Re: PROC REPORT create a vertical dividing line between variables/ Subtotal & GrandTotal labels

Posted in reply to Zeus_Olympous

I'm going to answer in a different order than your questions.

 

I ran your example in SAS University Edition.  There are warnings and notes in the log:

 

 WARNING: In a call to the CATX function, the buffer allocated for the result was not long enough to contain the concatenation of 
          all the arguments. The correct result would contain 20 characters, but the actual result might either be truncated to 10 
          character(s) or be completely blank, depending on the calling environment. The following note indicates the left-most 
          argument that caused truncation.
 NOTE: Argument 2 to function CATX(' ','Subtotal for'[12 of 20 characters shown],'          ') at line 1 column 10 is invalid.

The warnings and notes are for the compute after country block.  One issue is that the country variable has a length of 10, and the text you want to use is longer than 10 characters.  Another issue is that you are trying to concatenate the region variable.  The compute after country block only knows the value of country.

 

To use longer text in the country column, you need to adjust the length of the variable.

 

Suggested code:

 

proc sql;
  create table prdsale as
    select *
      from sashelp.prdsale;
     
  alter table prdsale
    modify country char(20) format=$20. ;
quit;

  
proc report data=prdsale(keep=country region actual predict) 
		style(summary)=Header spanrows
		style(column)={borderwidth=2}
		style(header)={borderwidth=2};
	column country region actual predict;
	define country / group;
	define region /group;
	define actual /analysis sum style(column)={borderrightcolor=red}
	                            style(header)={borderrightcolor=red};
	define predict /analysis sum;
	break after country / summarize;
	rbreak after /summarize;
    compute country;
      if upcase(_break_) = 'COUNTRY' then 
        do;
          country = catx(' ', country, 'Total');
        end;
      else if _break_ in ('_RBREAK_') then 
        do;
          country='Grand Total';
        end;
    endcomp;
run;

The borders in the output when using the HTMLBlue style in SAS University Edition are gray, so I chose red for the border between actual sales and predicted sales.

 

Output from suggested code:

 

PROC REPORT with red vertical bar between actual and predicted sales.jpg 

 

Two references:

 

The REPORT Procedure: A Primer for the Compute Block -- Jane Eslinger's paper at SAS Global Forum 2015.

 

Go Ahead and _BREAK_-down: Advanced COMPUTE Block Examples -- Cynthia Zender's paper at SAS Global Forum 2017.

 

 

Super User
Posts: 10,044

Re: PROC REPORT create a vertical dividing line between variables/ Subtotal & GrandTotal labels

Posted in reply to Zeus_Olympous

You could make a dummy variable.

 



proc report data=sashelp.class nowd;
columns name sex age dummy weight height;
define name/display;
define dummy/computed ' ' style={background=blue cellwidth=2px};
compute dummy/character length=1 ;
dummy=' ';
endcomp;
run;
Contributor
Posts: 45

Re: PROC REPORT create a vertical dividing line between variables/ Subtotal & GrandTotal labels

Ksharp, thank you very much, for your tip...I am going to use that as well!!

 

Thanks again!!

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 142 views
  • 1 like
  • 3 in conversation