The SAS Output Delivery System and reporting techniques

re: proc report spanrows not working

Accepted Solution Solved
Reply
Regular Contributor
Posts: 248
Accepted Solution

re: proc report spanrows not working

Hi....I am trying to get the spanrows to work for the variables Academic Year, Program and Enrollment Status and I can't seem to get it to work properly...any suggestions? Thanks.

 

PROC REPORT DATA=new222h MISSING ps=43 ls=108 NOWD spanrows
		style(report)={frame=box just=CENTER WIDTH=100%}
	STYLE(header)={font_weight=bold background=lightgrey font_face=Arial  
		font_size=8pt borderwidth=1px bordercolor=black just=CENTER}
	STYLE(COLUMN)={background=white font_face=Arial font_size=7pt borderwidth=1px 
		bordercolor=black}
	STYLE(LINES)={JUST=C FONT_WEIGHT=BOLD FONT_SIZE=11PT};

COLUMN ("Number of Students Enrolled by Program"(AcademicYear Major1 EnrollmentStatus RegisterStatus) ("Degree Earned"(No Yes Total)));

DEFINE AcademicYear / Group 'Academic Year';
DEFINE Major1 / Group 'Program' ;
DEFINE EnrollmentStatus / Group 'Enrollment Status';
DEFINE RegisterStatus / Group 'Register Status';
DEFINE No / analysis SUM 'No';
DEFINE Yes / analysis SUM 'Yes';
DEFINE Total / analysis SUM 'Total';

break after AcademicYear / summarize suppress;
	compute after AcademicYear;	
		Major1 = 'Total';
	 endcomp;

break after Major1 / summarize suppress;
	compute after Major1;	
		EnrollmentStatus = 'Total';
	 endcomp;

break after EnrollmentStatus  / summarize suppress;
	compute after EnrollmentStatus ;	
		RegisterStatus  = 'Total';
	 endcomp;

run;
quit;

Accepted Solutions
Solution
‎02-27-2018 06:35 PM
SAS Super FREQ
Posts: 9,424

Re: re: proc report spanrows not working

Hi:

  In my example, I would not put a total after DIVISION, because I only have 1 report row for each division, so a total would be unnecessary. I have highlighted with different colors which report lines are from which break statements. SPANROWS will only span the rows on the report that are coming from your data, directly. So if you were looking for SPANROWS to work on the green, pink or yellow highlighted lines, that's not how SPANROWS works. PROC REPORT inserted those highlighted lines on the report after SPANROWS had done its job with the spanning for the groups.

 

break_plus_spanrows.png

 

Here's the code I used.

proc sort data=sashelp.prdsale out=prdsale;
  where year = 1993;
  by year country region division;
run;

proc report data=prdsale spanrows;
title '1) break after year';
  column year country region division n actual predict;
  define year / group;
  define country / group;
  define region / group;
  define division / group;
  define n / 'Count';
  define actual / sum;
  define predict / sum;
  break after year / summarize;
run;


proc report data=prdsale spanrows;
title '2) break after year, country';
  column year country region division n actual predict;
  define year / group;
  define country / group;
  define region / group;
  define division / group;
  define n / 'Count';
  define actual / sum;
  define predict / sum;
  break after year / summarize;
  break after country / summarize;
run;

proc report data=prdsale spanrows;
title '3) break after year, country, region';
  column year country region division n actual predict;
  define year / group;
  define country / group;
  define region / group;
  define division / group;
  define n / 'Count';
  define actual / sum;
  define predict / sum;
  break after year / summarize;
  break after country / summarize;
  break after region / summarize;
run;

You can make the cells blank if you want or you could put text in, like SubTotal or Year Total.

 

Hope this helps,

cynthia

View solution in original post


All Replies
SAS Super FREQ
Posts: 9,424

Re: re: proc report spanrows not working

Hi:

  You posted your code, but not any sample data, so it's hard to guess.

 

  A few observations -- you don't say what your destination of interest is -- but since you are using ODS style overrides, I assume you are wanting HTML, RTF or PDF output. Some of your options like PS and LS will be ignored by most ODS destinations. I notice you have an override for STYLE(LINES) but no LINE statement in your program.

 

  But when I run a similar program using SASHELP.PRDSALE, and SPANROWS with PROC REPORT, here's what I get:

spanrows_example.png

I did a pretty vanilla report without any breaks or style changes, just to observe whether SPANROWS was working as I expected, and it seems to be working as I would expect.

 

  Here's the code I used:

proc report data=sashelp.prdsale spanrows;
  column year country region division n actual predict;
  define year / group;
  define country / group;
  define region / group;
  define division / group;
  define n / 'Count';
  define actual / sum;
  define predict / sum;
run;

Are there any messages in your LOG? Without data to test, it's hard to do more than guess just from your code. SPANROWS seems to be working correctly for me. You said in your post that you only wanted SPANROWS to work for Academic Year, Program and Enrollment Status, but Register Status is also a group item on the report, so SPANROWS will apply to that variable's column as well. All group or order variables on the report are impacted by SPANROWS.

 

cynthia

 

 

 

Regular Contributor
Posts: 248

Re: re: proc report spanrows not working

Posted in reply to Cynthia_sas

Hi Cynthia,

 

Thanks for your help. I am creating a pdf. I followed your example and removed the break after and spanrows worked like in your example. I would like to add Sub Totals after each grouping variables that it doesn't seem to want to work. There was no message in the log to indicate anything was wrong. In the example you have provided, if you were to include sub totals after Division, Region, Country and Year, would it still work?

Solution
‎02-27-2018 06:35 PM
SAS Super FREQ
Posts: 9,424

Re: re: proc report spanrows not working

Hi:

  In my example, I would not put a total after DIVISION, because I only have 1 report row for each division, so a total would be unnecessary. I have highlighted with different colors which report lines are from which break statements. SPANROWS will only span the rows on the report that are coming from your data, directly. So if you were looking for SPANROWS to work on the green, pink or yellow highlighted lines, that's not how SPANROWS works. PROC REPORT inserted those highlighted lines on the report after SPANROWS had done its job with the spanning for the groups.

 

break_plus_spanrows.png

 

Here's the code I used.

proc sort data=sashelp.prdsale out=prdsale;
  where year = 1993;
  by year country region division;
run;

proc report data=prdsale spanrows;
title '1) break after year';
  column year country region division n actual predict;
  define year / group;
  define country / group;
  define region / group;
  define division / group;
  define n / 'Count';
  define actual / sum;
  define predict / sum;
  break after year / summarize;
run;


proc report data=prdsale spanrows;
title '2) break after year, country';
  column year country region division n actual predict;
  define year / group;
  define country / group;
  define region / group;
  define division / group;
  define n / 'Count';
  define actual / sum;
  define predict / sum;
  break after year / summarize;
  break after country / summarize;
run;

proc report data=prdsale spanrows;
title '3) break after year, country, region';
  column year country region division n actual predict;
  define year / group;
  define country / group;
  define region / group;
  define division / group;
  define n / 'Count';
  define actual / sum;
  define predict / sum;
  break after year / summarize;
  break after country / summarize;
  break after region / summarize;
run;

You can make the cells blank if you want or you could put text in, like SubTotal or Year Total.

 

Hope this helps,

cynthia

Regular Contributor
Posts: 248

Re: re: proc report spanrows not working

Posted in reply to Cynthia_sas

Hi Cynthia....thanks for your help...it was very helpful.

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 352 views
  • 0 likes
  • 2 in conversation