Proc Report Append Table

[DavidPhillips2]

I am using SAS 9.2.  Is there a way to append a table using proc report?

[PoornimaRavishankar]

You want to append a table to a report created by PROC REPORT? Would help if you could add details.

[RW9]

Proc report is a function to create output not process data.  Use data step set statements or proc datasets append to get the data as you want prior to the proc report statement.

[DavidPhillips2]

Is there a way to use a sql dataset like the one I marked up on the left to display a report like the image on the right?  The last two rows of the dataset would be displayed as rows in the report as well. Is proc report the right tool for this? Note I do not want to do any calculations in proc report like summing.  I will use the calculate function to color.  I’m not sure how to display the header ‘Fall 2014’ above the data.

[Reeza]

You only need a subset of the data at the right and then could use proc tabulate.

Or if using proc report you could limit the data using a where clause though again I'd allow the proc to calculate the subtotal and totals.

[DavidPhillips2]

The last two rows at the bottom are the complication.  The calculate line at the bottom calculates the rate of change of the totals compared to the previous year.  Each year is displayed in a separate column in the report.  I researched this for three weeks and did not see a way to use proc tabulate to display a trailing calculated summary row.  Proc report has the calculate function however it cannot calculate this level of complexity.  Therefore I have to create these rows manually.

[Reeza]

Then why not create the data set and use proc print?

[DavidPhillips2]

Does proc print have features like the proc report spanrows and the ability to null out header titles like in the above example?  Can I color rows with it?

[ballardw]

Personally I suspect that starting from the data used to make the summary on the left might work.

Or custom formats to display blanks for the values of Null, subtotal and total.

Where clause to exclude the values you don't want.

I would start with something like:

proc tabulate data=have;

where location in ('Off campus','On campus');

class location;

class gender_desc;

class academic_period_desc;

var sum; /*you'll get some warnings about using a variable that is a keyword*/

table location=''*(gender_desc='' all='') All='',

     academic_period_desc=''*sum=''*f=comma6.

    ;

run;

[DavidPhillips2]

I’m confused.  How does this solution display the last two calculated rows?  I can’t use an All to show these.

[Reeza]

The problem is we're going off your pictures which isn't 100% of what you want, can you modify the images to actually show what you want?

[DavidPhillips2]

Sorry, I can see how that caused a lot of confusion.  Below is a complete picture.

[RW9]

Hi,

Well, there are lots of advice above about using report/print/tabulate.  Personally I don't use any of them to do my outputs.  I start with an empty table the way I want the output to look, then insert the data, e.g.

proc sql;

     create table WANT

     (

          COL1 char(200),

          COL2 char(200),

          YEAR1 char(200),

          ...

     );

     set COL1="Off campus",

          COL2="Female",

           YEAR1=(select put(count(FALL_2010),best.) from HAVE where SEX="F"),

          ...

     ...

quit;

Whilst the seems too much typing, then combine it with do loops/arrays, data _null_ and call execute and you can quickly build up large reports in a small datastep.

[Ksharp]

Maybe LINE statement could help you .

compute after;

line 'xxxxxxxxxxxx';

endcomp;

Xia Keshan

[Cynthia_sas]

Hi:

  I think that if you are going to go forward with pre-summarizing your data and want to be sure of the order and the highlighting, then you can get what you want. Assuming that you have a whole other set of ROWS for every year, allowed me to use ACROSS as the usage for the Academic Period, but also meant that the format change had to be done with absolute column numbers.

  I made some "fake" data to show you the helper variable ROWORD that I made to help simplify the highlighting. Since you did not show the structure of your input data, for multiple years, I made some data for multiple years. In order to simplify the percents, I divided the percent number in the last row by 100 because then I could use a predefined percent format which would do an automatic multiply by 100. Also, in some of the places where you had Null, I "filled" in with values to simplify using GROUP usage in PROC REPORT. Here's the code that produced the above screen shot.

Cynthia

** make some data;
** because so many "nulls", needed to make a helper variable to establish the ORDER of the data;
** so it was the same for every year and also needed to populate LOC with the right value instead of "Null";
** also need to modify the table if using multiple years;
** copied some numbers for 2013;
data only2013_2014;
  length loc $25 gender $20 acad_per_des $20;
  infile datalines dlm=',' dsd;
  input roword loc $ gender $ acad_per_des $ val ;
  ** adjust the percent so we can use the percentn format (which multiplies by 100);
  if roword = 11 then val = val / 100;
datalines;
1,"Off campus", Female, "Fall 2014", 882
2,"Off campus", Male, "Fall 2014", 308
3,"Off campus",N/R,"Fall 2014",254
4,"Off campus",Subtotal,Fall 2014,1444
5,"On campus",Female, "Fall 2014", 16783
6,"On campus",Male, "Fall 2014",12711
7,"On campus",N/R,"Fall 2014",135
8,"On campus",Subtotal,"Fall 2014",29719
9,Total,Total,"Fall 2014",31163
10,"Rate of Change calc1",Null,"Fall 2014", -125
11,"Rate of Change calc2",Null,"Fall 2014",-0.40
1,"Off campus", Female, "Fall 2013", 818
2,"Off campus", Male, "Fall 2013", 342
3,"Off campus",N/R,"Fall 2013",236
4,"Off campus",Subtotal,Fall 2013,1396
5,"On campus",Female, "Fall 2013", 16974
6,"On campus",Male, "Fall 2013",12791
7,"On campus",N/R,"Fall 2013",127
8,"On campus",Subtotal,"Fall 2013",29892
9,Total,Total,"Fall 2013",31288
10,"Rate of Change calc1",Null,"Fall 2013", -464
11,"Rate of Change calc2",Null,"Fall 2013",-1.46
;
run;
 
ods html file='c:\temp\hilite.html';
  
proc report data=only2013_2014 nowd;
  title '1) change style based on row type show ROWORD variable';
  column  loc gender roword val,acad_per_des ;
  define loc / group order=data ' ';
  define gender / group order=data ' ';
  define roword / group order=data   ;
  define acad_per_des / across;
  define val / sum analysis ' ' f=comma8.;
  compute roword;
    if roword in (4, 😎 then do;
       call define (_row_,'style','style={background=lightblue}');
   end;
   else if roword in (9,10,11) then do;
    call define (_row_,'style','style={background=yellow}');
   end;
  endcomp;
  compute gender;
    if loc = 'Total' then gender = ' ';
    else if gender = 'Null' then gender = ' ';
  endcomp;
  compute val;
    if roword = 11 then do;
       call define ('_c4_','format','percentn10.2}');
       call define ('_c5_','format','percentn10.2}');
    end;
  endcomp;
run;
     
proc report data=only2013_2014 nowd;
  title '2) change style based on row type use NOPRINT for ROWORD variable';
  column  loc gender roword val,acad_per_des ;
  define loc / group order=data ' ';
  define gender / group order=data ' ';
  define roword / group order=data noprint  ;
  define acad_per_des / across ' ';
  define val / sum analysis ' ' f=comma8.;
  compute roword;
    if roword in (4, 😎 then do;
       call define (_row_,'style','style={background=lightblue}');
   end;
   else if roword in (9,10,11) then do;
      call define (_row_,'style','style={background=yellow}');
    end;
  endcomp;
  compute gender;
    if loc = 'Total' then gender = ' ';
    else if gender = 'Null' then gender = ' ';
  endcomp;
  compute val;
    if roword = 11 then do;
       call define ('_c4_','format','percentn10.2}');
       call define ('_c5_','format','percentn10.2}');
    end;
  endcomp;
run;

ods html close;