BookmarkSubscribeRSS Feed
DavidPhillips2
Rhodochrosite | Level 12

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

14 REPLIES 14
PoornimaRavishankar
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Rhodochrosite | Level 12

Rate of change.png

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
Super User

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
Rhodochrosite | Level 12

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
Super User

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

DavidPhillips2
Rhodochrosite | Level 12

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
Super User

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
Rhodochrosite | Level 12

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

Reeza
Super User

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
Rhodochrosite | Level 12

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

Rate of change.png

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

Maybe LINE statement could help you .

compute after;

line 'xxxxxxxxxxxx';

endcomp;

Xia Keshan

Cynthia_sas
SAS Super FREQ

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.

hilite_report.png

  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;

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
  • 14 replies
  • 4912 views
  • 3 likes
  • 7 in conversation