topic Re: PROC REPORT - Conditional Suppress a Row (obs) in SAS Procedures

PROC REPORT - Conditional Suppress a Row (obs)

CurtisER — Wed, 13 Jan 2016 15:25:46 GMT

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.

Re: PROC REPORT - Conditional Suppress a Row (obs)

RW9 — Wed, 13 Jan 2016 15:36:45 GMT

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;

Re: PROC REPORT - Conditional Suppress a Row (obs)

CurtisER — Wed, 13 Jan 2016 16:10:32 GMT

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.

Re: PROC REPORT - Conditional Suppress a Row (obs)

Reeza — Wed, 13 Jan 2016 16:14:32 GMT

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.

Re: PROC REPORT - Conditional Suppress a Row (obs)

CurtisER — Wed, 13 Jan 2016 17:22:44 GMT

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.

Re: PROC REPORT - Conditional Suppress a Row (obs)

RW9 — Wed, 13 Jan 2016 18:03:49 GMT

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.

Re: PROC REPORT - Conditional Suppress a Row (obs)

Ksharp — Thu, 14 Jan 2016 03:23:48 GMT

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 .

Re: PROC REPORT - Conditional Suppress a Row (obs)

CurtisER — Thu, 14 Jan 2016 14:28:34 GMT

@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.