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

In SAS 9.2 I’m converting over some proc tabulate statements to proc report.

I’m having a hard time converting over tabulate statements with a total and subtotal.

For some reason statements like dol and dul are not working for me.  Any advice on converting this to proc report or why dol and dul are not working for me?

PROC TABULATE missing

  data=sashelp.orsales;

  class year product_line product_category;

  var quantity;

  TABLES product_line=' ' * (product_category=' ' all= 'Sub-total') all='Total',

  year=' '*quantity=' '*sum=' '  /row=float;

  KEYLABEL n=' ' Sum= ' ';

  run;

top.png

The closest I have is:

proc report data=sashelp.orsales;

  column product_line product_category quantity, year;

  define product_line / group;

  define product_category / group;

  define year / across ' ';

  define quantity / sum ' ' FORMAT=comma8.;

  break after product_line / dol dul summarize style={background=lightblue font_weight=bold };

  rbreak after / ul summarize style={background=yellow font_weight=bold };

  run;

         bottom.png

1 ACCEPTED SOLUTION

Accepted Solutions
DavidPhillips2
Rhodochrosite | Level 12

proc report data=sashelp.orsales;

  column product_line product_category quantity, year;

  define product_line / group;

  define product_category / group;

  define year / across ' ';

  define quantity / sum ' ' FORMAT=comma8.;

  break after product_line /  summarize style={background=lightblue font_weight=bold };

  compute after product_line;

  product_line = 'Sub Total';

  endcomp;

  rbreak after / summarize style={background=yellow font_weight=bold };

  compute after;

  product_line = 'Total';

  endcomp;

  run;

total.png

View solution in original post

3 REPLIES 3
ballardw
Super User

DOL and DUL: From the online documentation:

This option has no effect on ODS destinations other than traditional SAS

monospace output

DavidPhillips2
Rhodochrosite | Level 12

proc report data=sashelp.orsales;

  column product_line product_category quantity, year;

  define product_line / group;

  define product_category / group;

  define year / across ' ';

  define quantity / sum ' ' FORMAT=comma8.;

  break after product_line /  summarize style={background=lightblue font_weight=bold };

  compute after product_line;

  product_line = 'Sub Total';

  endcomp;

  rbreak after / summarize style={background=yellow font_weight=bold };

  compute after;

  product_line = 'Total';

  endcomp;

  run;

total.png

DavidPhillips2
Rhodochrosite | Level 12

I ran into a bit of complication when I tried to use formats.  The catch is I have all of my data stored as numbers.  Then I use format blocks to format to text.  The total line does not show up using the rbreak command.  When I display text without format it displays ok. The general concept is below with macro variables.

proc report data=enrollment;

  column &reportTy1 &reportTy2 &measureTy, academic_period_desc;

  define &reportTy1 / group ' ';

  define &reportTy2 / group ' ';

  define academic_period_desc / across ' ';

  define &measureTy / sum ' ' FORMAT=comma8.;

  break after &reportTy1 /  summarize style={background=lightblue font_weight=bold };

  compute after &reportTy1;

  %IF &reportTy1FormatFlag = Yes %then %do;

  &reportTy1 = 98;

  %end;

  %else %do;

  &reportTy1 = 'Subtotal';

  %end;

  endcomp;

  rbreak after / summarize style={background=yellow font_weight=bold };

  compute after;

  %IF &reportTy1FormatFlag = Yes %then %do;

  &reportTy1 = 99;

  %end;

  %else %do;

  &reportTy1 = 'Total';

  %end;

  endcomp;

  COMPUTE &reportTy1;

        %IF &reportTy1 NE . %THEN &reportTy1;

        %ELSE &reportTy1=' ';

        %IF _break_='_RBREAK_' %THEN

    &reportTy1=99;

      ENDCOMP;

  &formatOrderClause;

  where four_digit_year = "&maxPeriod";

  run;

missing total.png

sas-innovate-2024.png

 

Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.

 

Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 

 

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
  • 3 replies
  • 1632 views
  • 0 likes
  • 2 in conversation