Using ARRAYs in a PROC REPORT COMPUTE block

[PaigeMiller]

Way back in 2023, @Ksharp produced this example of using an ARRAY in a PROC REPORT compute  block. In that example, there was no ACROSS variable. My question today is can this be done with an ACROSS variable, where the columns names are _Cn_??

Example: here I have DATE as an ACROSS variable, and in the COMPUTE block I have created two lines that change the background color if the value of the variable is >0.1. But I had to type those lines myself, and there are many more that I haven't typed. I am wondering if an ARRAY can work here which covers all columns of the ACROSS variable. If so, how? (Also, I'm sure I can write a macro to do this, but the question is specifically can an ARRAY be used and how?)

Example:

data WORK.example;
  infile datalines dsd truncover;
  input date state:$4. rate:32.;
datalines4;
2013,FL,0.1355932203
2013,MA,0.0294117647
2013,NY,0.0977443609
2014,FL,0.0487804878
2014,MA,0.0606060606
2014,NY,0.0614035088
2015,FL,0.0833333333
2015,MA,0.0487804878
2015,NY,0.0625
2016,FL,0.071942446
2016,MA,0.1020408163
2016,NY,0.0756302521
2017,FL,0.0508474576
2017,MA,0.0277777778
2017,NY,0.0714285714
2018,FL,0.0112359551
2018,MA,0
2018,NY,0.0561797753
2019,FL,0.0438596491
2019,MA,0.0689655172
2019,NY,0.0784313725
2020,FL,0.0347222222
2020,MA,0
2020,NY,0.0333333333
2021,FL,0.0105263158
2021,MA,0
2021,NY,0.0178571429
2022,FL,0.0188679245
2022,MA,0.0833333333
2022,NY,0
2023,FL,0
2023,MA,0
2023,NY,0.0454545455
2024,FL,0
2024,MA,0
2024,NY,0
;;;;

proc report data=example;
    columns state date,rate dummy;
    define state/group;
    define date/across;
    define rate/mean format=percent8.2;
    define dummy/noprint;
    compute dummy;
        if _c2_>0.1 then call define ("_c2_",'style','style={background=lightmoderatered}');
        if _c3_>0.1 then call define ("_c3_",'style','style={background=lightmoderatered}');
    endcompute;
run;

[FreelanceReinh · Accepted solution]

Hello @PaigeMiller,

Seems to work if you type the list of variable names _c2_ _c3_ ... in the ARRAY statement or create it, e.g., with a little macro:

%macro _c(n);
%do n=2 %to &n; _c&n._ %end;
%mend _c;

proc report data=example;
    columns state date,rate dummy;
    define state/group;
    define date/across;
    define rate/mean format=percent8.2;
    define dummy/noprint;
    compute dummy;
      array r[*] %_c(99);
      do i=1 to dim(r);
        if r[i]>0.1 then call define (vname(r[i]),'style','style={background=lightmoderatered}');
      end;
    endcomp;
run;

[PaigeMiller]

I had not considered a "hybrid" of small macro plus ARRAYs. Thanks, I'm sure that will work. If anyone else has suggestions about a different approach, please let me know.

[Tom]

Don't need to use macro to generate the list of column references.  For example you could use PROC SQL.

proc sql noprint;
 select cats('_c',1+monotonic(),'_') into :list separated by ' '
 from (select distinct date from example);
quit;

proc report data=example;
    columns state rate,date dummy;
    define state/group;
    define date/across;
    define rate/mean format=percent8.2 ' ';
    define dummy/noprint;
    compute dummy;
      array r[*]  &list ;
      do i=1 to dim(r);
        if r[i]>0.1 then call define (vname(r[i]),'style','style={background=lightmoderatered}');
      end;
    endcomp;
run;

If the across variable is not at position 2 then change the offset in the SQL code from 1 to the appropriate number.

[mkeintz]

@Tom 

A very nice use case for the undocumented "monotonic" function in PROC SQL.

[Ksharp]

Paige,

I think it is very hard to do this .  Maybe @Cynthia_sas knew how to do it ?

Here is another simple way to do it .

data WORK.example;
  infile datalines dsd truncover;
  input date state:$4. rate:32.;
  if rate>0.1 then _rate=cats('(*ESC*){style [background=lightmoderatered]',put(rate,percent8.2),'}');
    else _rate=put(rate,percent8.2);
datalines4;
2013,FL,0.1355932203
2013,MA,0.0294117647
2013,NY,0.0977443609
2014,FL,0.0487804878
2014,MA,0.0606060606
2014,NY,0.0614035088
2015,FL,0.0833333333
2015,MA,0.0487804878
2015,NY,0.0625
2016,FL,0.071942446
2016,MA,0.1020408163
2016,NY,0.0756302521
2017,FL,0.0508474576
2017,MA,0.0277777778
2017,NY,0.0714285714
2018,FL,0.0112359551
2018,MA,0
2018,NY,0.0561797753
2019,FL,0.0438596491
2019,MA,0.0689655172
2019,NY,0.0784313725
2020,FL,0.0347222222
2020,MA,0
2020,NY,0.0333333333
2021,FL,0.0105263158
2021,MA,0
2021,NY,0.0178571429
2022,FL,0.0188679245
2022,MA,0.0833333333
2022,NY,0
2023,FL,0
2023,MA,0
2023,NY,0.0454545455
2024,FL,0
2024,MA,0
2024,NY,0
;;;;
ods rtf file='c:\temp\temp.rtf' style=journal;
proc report data=example nowd;
    columns state date,_rate ;
    define state/group;
    define date/across;
    define _rate/group;
run;
ods rtf close;

[Cynthia_sas]

Hi:

  Well, if I couldn't use a FORMAT, which would avoid the need for using the COMPUTE block:

 

Then I'd probably use some kind of macro program to build the COMPUTE block. I tend to avoid the use of monotonic since 1) it is undocumented and 2) there was a TS note that is might produce undesirable results, so I'd probably just use a different procedure to determine how many years there were and then use that value to generate the ARRAY/COMPUTE block.

  But for a simple example like this, my choice would be a user-defined format for the background.

Cynthia

[PaigeMiller]

Thanks, all! Very helpful.

Side note: to @Cynthia_sas  Your comments about MONOTONIC() agree with my opinions about not using it, as I don't really grasp all the situations where trouble could arise. Nevertheless, I attended a talk by someone from SAS (I don't remember the person's name) on the subject of when to use a DATA step and when to use SQL, and in that talk, the use of MONOTONIC() was actually recommended. Seems like two different SAS employees are on opposite sides of this issue, and I would have thought that there would be some agreement at least among SAS employees about not using MONOTONIC().

[Cynthia_sas]

Hi, @PaigeMiller -- I'm sure that Monotonic does work in some usage scenarios. However, as someone who used to work with attorneys, producing reports for discovery and testimony, I could only use supported features of the software. So, THIS note: https://support.sas.com/kb/15/138.html is the reason I do not personally use monotonic in any of my code. I'm sure it's fine if others want to try it. I just don't want to find what undesirable means when it's my code and results under the magnifying glass.
Cynthia

[PaigeMiller]

I agree, I won't use it either, since I don't understand what situations it doesn't work, and since it is unsupported it might not work in the next upgrade of SAS. I was shocked when listening to the presentation that no warnings about the function is unsupported were given.