Help using Base SAS procedures

PROC REPORT - Conditional Suppress a Row (obs)

Reply
Occasional Contributor
Posts: 17

PROC REPORT - Conditional Suppress a Row (obs)

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.

Super User
Super User
Posts: 7,942

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

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;
Occasional Contributor
Posts: 17

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

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.

Super User
Posts: 19,770

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

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. 

 

 

Occasional Contributor
Posts: 17

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

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
         
COMPSchedulePeriodEMPJOLDOSTSRC
DIFFA1201511.....1
OLD  1312572003711
NEW  1312572003790
DIFFA2201511...11.
OLD  10127124404890
NEW  101271244-1-190
DIFFTotal  ..11

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.

Super User
Super User
Posts: 7,942

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

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.

Super User
Posts: 10,020

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

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 .

Occasional Contributor
Posts: 17

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


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.

Ask a Question
Discussion stats
  • 7 replies
  • 577 views
  • 1 like
  • 4 in conversation