topic Re: Methods to hide rows in PROC TABULATE in SAS Programming

Methods to hide rows in PROC TABULATE

Quentin — Tue, 30 Apr 2024 21:30:35 GMT

Wondering about methods to hide a row in PROC TABULATE.

Suppose I have a simple table with DATE in rows, PASS in column and have a total row. So daily data for April like:

data have ;
  do date='01Apr2024'd to '30Apr2024'd ;
    do id=1 to 5 ;
      pass=ranuni(9)<.3 ;
      output ;
    end ;
  end ;
  format date date9. ;
run ;

proc tabulate data=have;
  class date pass ;
  tables (date all),pass ;
run ;

I want to make that table, but show only the rows for Apr 24 -Apr 30, and the ALL row. But I want the ALL row to reflect all 30 days, even though the table only shows rows for 7 days.

In my mind, I want the table as above, but just hide the rows for Apr 1 - Apr 23.

My first thought is to extend my data to have data as is for April 24-30, and then output all the rows again with date set to some arbitrary value that I can use to make an "ALL" row. So something like:

data want ;
  set have ;
  if date>='24Apr2024'd then output ;
  date='09Sep2099'd ;
  output ;
run ;

proc format ;
  value datef
  '09Sep2099'd='01Apr2024 - 30Apr2024'
  other=[date9.]
  ;
run ;

proc tabulate data=want;
  class date pass ;
  tables date,pass ;
  format date datef. ;
run ;

That works and gives me the desired output:

 ----------------------------------------------------------
 |                              |          pass           |
 |                              |-------------------------|
 |                              |     0      |     1      |
 |                              |------------+------------|
 |                              |     N      |     N      |
 |------------------------------+------------+------------|
 |date                          |            |            |
 |------------------------------|            |            |
 |24APR2024                     |        3.00|        2.00|
 |------------------------------+------------+------------|
 |25APR2024                     |        3.00|        2.00|
 |------------------------------+------------+------------|
 |26APR2024                     |        4.00|        1.00|
 |------------------------------+------------+------------|
 |27APR2024                     |        3.00|        2.00|
 |------------------------------+------------+------------|
 |28APR2024                     |        5.00|           .|
 |------------------------------+------------+------------|
 |29APR2024                     |        4.00|        1.00|
 |------------------------------+------------+------------|
 |30APR2024                     |        4.00|        1.00|
 |------------------------------+------------+------------|
 |01Apr2024 - 30Apr2024         |      113.00|       37.00|
 ----------------------------------------------------------

But I'm curious about other options. It almost feels like I could do this with a MULTILABEL format and a CLASSDATA dataset, but in the end I don't think that will work.

I will be using ODS for the output (probably ODS POWERPOINT). So I guess I could try setting a STYLE attribute like ROWHEIGHT=0 for some rows (using CLASSLEV), and see if TABULATE will let me 'hide' a row that way.

Re: Methods to hide rows in PROC TABULATE

Quentin — Tue, 30 Apr 2024 22:05:12 GMT

My thought to use a conditional HEIGHT style on CLASSLEV failed. Looks like you can use conditional heights, but if you ask for a height that is 0, it gets ignored rather than hiding the row.

proc format ;
  value hide
  '01Apr2024'd-'23Apr2024'd='0in'
  other='1in'
  ;
run ;

proc tabulate data=have;
  class date pass;
  classlev date /style={height=hide.};
  tables date*{style=<parent>},pass ;
  format date datef. ;
run ;

Re: Methods to hide rows in PROC TABULATE

Ksharp — Wed, 01 May 2024 01:58:29 GMT

@Quentin

1)Yes. You can use MULTILABEL option of proc format to get it,but that is too clumsy.Here is an example.

data have ;
  do date='01Apr2024'd to '30Apr2024'd ;
    do id=1 to 5 ;
      pass=ranuni(9)<.3 ;
      output ;
    end ;
  end ;
  format date date9. ;
run ;

proc format ;
  value datef(multilabel notsorted)
  '24Apr2024'd='24Apr2024'
  '25Apr2024'd='25Apr2024'
  '26Apr2024'd='26Apr2024'
  '27Apr2024'd='27Apr2024'
  '28Apr2024'd='28Apr2024'
  '29Apr2024'd='29Apr2024'
  '30Apr2024'd='30Apr2024'
  '01Apr2024'd-'30Apr2024'd='01Apr2024 - 30Apr2024'
  ;
run ;

proc tabulate data=have;
format date datef.;
  class date/mlf preloadfmt order=data;
  class pass ;
  tables date,pass /printmiss;
run ;

2) I think the most flexible way is using PROC SQL. If you like it ,I would present it .

Re: Methods to hide rows in PROC TABULATE

Quentin — Sat, 04 May 2024 11:55:03 GMT

Thanks @Ksharp !

I thought it would be possible with MLF, but I was stuck on the idea of using CLASSDATA to select the rows to display, forgetting that you can do it with preloadfmt order=data.

I like the MLF approach, and was planning to use a macro to build the format anyway.

If you'd like to show your SQL approach, I'd be happy to see it. But for my real problem I'll probably stick with TABULATE, since the real table is more complex, with different statistics calculated by TABULATE, trafficlighting, etc.

And sorry for not responding sooner. I somehow missed the notification when you responded. Luckily I got the reminder email today that said "you got responses."

Re: Methods to hide rows in PROC TABULATE

Ksharp — Sun, 05 May 2024 07:33:46 GMT

Actually I would like to use PROC SQL which is most flexible way to handle REPORT problem, and not need to list all these levels one by one and could produce any form/style report which PROC TABULATE is unable to do.

data have ;
  do date='01Apr2024'd to '30Apr2024'd ;
    do id=1 to 5 ;
      pass=ranuni(9)<.3 ;
      output ;
    end ;
  end ;
  format date date9. ;
run ;

proc sql;
create table want as
select put(date,date9.) as date,pass,count(*) as count
 from have 
  where date>='24Apr2024'd
   group by date,pass
union all
select 'All',pass,count(*) 
 from have
  group by pass
;
quit;
proc report data=want nowd;
column date pass,count;
define date/group order=data;
define pass/across ;
define count/analysis sum 'N';
run;

Re: Methods to hide rows in PROC TABULATE

Quentin — Sun, 05 May 2024 15:22:14 GMT

Thanks again @Ksharp . I don't think I've ever used UNION without CORR. Cool approach.