PROC REPORT - Conditional Suppress a Row (obs)

[CurtisER]

I want to suppress a particular row if one of the value meets the condition in a PROC REPORT.  I still need that row but not printed/displayed.

For example, I have a data that looks like this:

SCHED_ID  PERIOD  COMP  RC  TD

--------  ------  ----  --  --

A1        201501  OLD   .   .

A1        201501  NEW   1   9

A1        201501  DIF   1   1

A2        201501  OLD   1   1

A2        201501  NEW   1   1

A2        201501  DIF   .   .

I need to suppress the printing of rows with 'DIF' under COMP but I need to keep 'DIF' to get the cumulative counts.

I know using COMPUTE / ENDCOMP block is the way to go but how do I check the condition COMP='DIF' inside the block and suppress that row while still counting values under RC and TD.

Thanks.

[RW9]

Whilst it may be possible in proc report to do processing such as this, sure someone else will post on that, I would always do my calculations/processing in a datastep prior to any output procedure, so that I can store the data before reporting for validation purposes and to keep the report procedure clean.  

Therefore, in a datastep, do your sums ignoring those rows, then save that, and feed that into the proc report.  Note this is not tested, but something along these lines/

data for_report;
  set have;
  by sched_id;
  retain sum_rc sum_td;
  if first.sched_id then do;
    sum_rc=0;
    sum_td=0;
  end;
  if comp ne "DIF" then do;
    sum_rc=sum_rc+rc;
    sum_td=sum_td+td;
  end;
  if last then do;
    output;
    sched_id="Total";
    rc=sum_rc;
    td=sum_td;
    output;
  end;
  else output;
run;

[CurtisER]

Only the DIFF should be summed (and the user wants the diff highlighted in the OLD/NEW row).  So, I use the value of 1 in DIFF to put in a highlight on a specific cell that changed.  The user also wants a total count of DIFF at the end (but not the sum of OLD/NEW).

Hence, that's why I need the DIFF row to keep and to suppress.  Sorry, I didn't add this bit in the original post.

Thanks.

[Reeza]

What does your proc report look like currently?

You might be able to do a conditional format on the variable instead, or possibly a custom format. 

[CurtisER]

Do you mean a PROC REPORT code?  Or the actual report?

Currently the code is a simple PROC REPORT -- no COMPUTE/ENDCOMP block as of yet.

   proc report data = microdata_changes_check2
               style(report header lines summary calldef column) = {FONT_FACE = Palatino} NOWD;
      title1 "Microdata Changes";
      title2 "Run Date: &SYSDATE9.";
  
      columns comp sched period emp jo ld os ts rc;
  
      define comp / STYLE(HEADER) = {FONT_WEIGHT = BOLD};
      define sched / group center STYLE(HEADER) = {FONT_WEIGHT = BOLD};
      define period / group center STYLE(HEADER) = {FONT_WEIGHT = BOLD};
      define emp / center STYLE(HEADER) = {FONT_WEIGHT = BOLD};
      define jo / center STYLE(HEADER) = {FONT_WEIGHT = BOLD};
      define ld / center STYLE(HEADER) = {FONT_WEIGHT = BOLD};
      define os / center STYLE(HEADER) = {FONT_WEIGHT = BOLD};
      define ts / center STYLE(HEADER) = {FONT_WEIGHT = BOLD};
      define rc / center STYLE(HEADER) = {FONT_WEIGHT = BOLD};
   run;

It's sorted by sched period.

The output uses tagsets.ExcelXP:

Microdata Changes
Run Date: 13JAN2016
COMP	Schedule	Period	EMP	JO	LD	OS	TS	RC
DIFF	A1	201511	.	.	.	.	.	1
OLD			1312	572	0	0	37	11
NEW			1312	572	0	0	37	90
DIFF	A2	201511	.	.	.	1	1	.
OLD			10127	124	4	0	48	90
NEW			10127	124	4	-1	-1	90
DIFF	Total			.	.	1	1	1

As you can see, I want to suppress the rows with DIFF so that the schedule/period falls on the OLD line.  And the final line shows counts of DIFF.  The text in orange are the hightlighted cells in orange (I can't seem to add a highlight in this post). 

The spreadsheet layout is what the user wants.

So, 1) get that DIFF row suppressed in the output (the user only wants to see OLD/NEW);

2) use the DIFF row in each columns to find which cells to highlight; and

3) sum up DIFF in each columns to get the total.

Thanks.

[RW9]

Well, you know your data and output best.  My point is to do all the necessary calculations in a datastep *before* the proc report.  What calculations, flags etc. you setup is up to you.  I.e. so you end up with a dataset which looks similar to the output report, with the flags needed for the traffic lighting.

[Ksharp]

No . you can't Suppress a Row in proc report . 

One thing I can image is using call define() to make this row ' forecolor be white which is same as the report's background, and might be hide it but still will print it .

Maybe you should post it at ODS and Base Reporting  and see Cynthia@sas if have some better idea .

[CurtisER]

---

@Ksharp wrote:

No . you can't Suppress a Row in proc report . 

One thing I can image is using call define() to make this row ' forecolor be white which is same as the report's background, and might be hide it but still will print it .

---

1)  That's my conclusion I've come to that a row cannot be suppressed in PROC REPORT.

2)  That's a good idea and I'll play around with this idea.

Thanks all for the responses.  I have what I need to know.