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

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

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

4 REPLIES 4
Cynthia_sas
SAS Super FREQ

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

 

 

 

twildone
Pyrite | Level 9

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?

Cynthia_sas
SAS Super FREQ

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

twildone
Pyrite | Level 9

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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