Proc report: define variables with loops and macro use.

[need_some_help]

Hi!

There is another problem again.

I'm doing a report where duration of working time is shown for every employee from department (for a month). So the first column is the list of employees and then 31columns (each for a day of month) go. In each column if there is the value greater than the certain threshold it is highlighted and etc. (other cell(depends on value)-manipulations). So the same procedure repeats for all 31 columns. So I'm interested if there is the way to 'automate' column defining in proc report?

I've tried something but unsuccessfully. And all this macro using seems to be not so easy - so I need help.

To show the problem (let it be a 2-day month):

/*work_time given in seconds*/
data worktime_pre;
input agent$ d1 d2;
datalines;
emp1 3720 0
emp2 4860 3120
emp3 0 2580
emp4 0 2040
;
run;

/*as I work in SAS-EG some changes are done through Query-builder
and the problem ones are the column names changings*/
proc sql;
create table worktime as
select
  agent as 'agent'n,
  d1 LABEL='' AS '1'n,
        d2 LABEL='' AS '2'n
from worktime_pre;
quit;

/* maybe there is wrong using macro variables with quotes?? */
%MACRO defining(day);
define '&day'n / order missing noprint order=data;
define n&day / computed '&day' missing order=data;
compute n&day / character;  
  if '&day'n=0 then n&day='-';
   else do;
    n&day=strip(put('&day'n,hhmm10.));
    CALL DEFINE(_COL_, "style", "STYLE=[BACKGROUND=ALICEBLUE]");
   end;
endcomp;
%MEND defining;

%macro month_report;
proc report data=WORK.worktime(FIRSTOBS=1) nowd
style(report)={just=center}
style(header)={just=center}
style(column)={just=center};
column 'agent'n '1'n n1 '2'n n2 ;

define 'agent'n / order 'agent' missing order=data;

/*define '1'n / order missing noprint order=data;
define n1 / computed '1' missing order=data;
compute n1 / character;  
  if '1'n=0 then n1='-';
   else do;
    n1=strip(put('1'n,hhmm10.));
    CALL DEFINE(_COL_, "style", "STYLE=[BACKGROUND=ALICEBLUE]");
   end;
endcomp;

define '2'n / order missing noprint order=data;
define n2 / computed '2' missing order=data;
compute n2 / character;  
  if '2'n=0 then n2='-';
   else do;
    n2=strip(put('2'n,hhmm10.));
    CALL DEFINE(_COL_, "style", "STYLE=[BACKGROUND=ALICEBLUE]");
   end;
endcomp;*/

/*'automating'....*/
%do i=1 %to 2;
%defining(&i);
%end;

run;
quit;

%mend month_report;

%month_report;

[need_some_help]

Sorry for bad formatting - it has corrupted when copying.

[Cynthia_sas]

Hi:

  You may be making the problem harder than it is. Depending on how your data are structured, you can use simple user-defined formats to do trafficlighting. For example, assume that your data had this structure (showing 10 days for Alice and a few days for Bob):

empname    month    day    year    dateval    hours

Alice       7        1    2011    01JUL11      3.3

Alice       7        2    2011    02JUL11      3.8

Alice       7        3    2011    03JUL11      8.1

Alice       7        4    2011    04JUL11      4.4

Alice       7        5    2011    05JUL11     13.2

Alice       7        6    2011    06JUL11      8.2

Alice       7        7    2011    07JUL11      5.3

Alice       7        8    2011    08JUL11     12.2

Alice       7        9    2011    09JUL11     30.3

Alice       7       10    2011    10JUL11     12.4

Bob         7        1    2011    01JUL11      1.6

Bob         7        2    2011    02JUL11      5.7

Bob         7        3    2011    03JUL11      7.4

Bob         7        4    2011    04JUL11     10.9

Bob         7        5    2011    05JUL11     14.1

Bob         7        6    2011    06JUL11      0.7

So, then you could simply generate the output, with trafficlighting in PROC REPORT, as shown in the attached screen shot. The program shown below generated the screen shot. Because the dateval variable was used as an ACROSS item with HOURS supplying the value for each unique column, I only needed to use the special format in the STYLE= override for the HOURS variable...that means you would not need to use MACRO or CALL DEFINE to generate a report. I only made 10 days of data because it doesn't matter whether there are 10 days or 31 days -- the format will be used for all values of the HOURS variable.

cynthia

** the program;

** Make Some data for 10 days for 5 employees;

data makeemp;

  infile datalines;

  input empname $ month day year dateval : date7. hours;

  format dateval date7.;

return;

datalines;

Alice       7        1    2011    01JUL11      3.3

Alice       7        2    2011    02JUL11      3.8

Alice       7        3    2011    03JUL11      8.1

Alice       7        4    2011    04JUL11      4.4

Alice       7        5    2011    05JUL11     13.2

Alice       7        6    2011    06JUL11      8.2

Alice       7        7    2011    07JUL11      5.3

Alice       7        8    2011    08JUL11     12.2

Alice       7        9    2011    09JUL11     30.3

Alice       7       10    2011    10JUL11     12.4

Bob         7        1    2011    01JUL11      1.6

Bob         7        2    2011    02JUL11      5.7

Bob         7        3    2011    03JUL11      7.4

Bob         7        4    2011    04JUL11     10.9

Bob         7        5    2011    05JUL11     14.1

Bob         7        6    2011    06JUL11      0.7

Bob         7        7    2011    07JUL11      9.5

Bob         7        8    2011    08JUL11      1.4

Bob         7        9    2011    09JUL11     11.8

Bob         7       10    2011    10JUL11      8.6

Carl        7        1    2011    01JUL11      1.4

Carl        7        2    2011    02JUL11      7.3

Carl        7        3    2011    03JUL11      9.9

Carl        7        4    2011    04JUL11      8.7

Carl        7        5    2011    05JUL11     12.2

Carl        7        6    2011    06JUL11      3.1

Carl        7        7    2011    07JUL11     18.4

Carl        7        8    2011    08JUL11     23.1

Carl        7        9    2011    09JUL11     22.7

Carl        7       10    2011    10JUL11      0.8

Diane       7        1    2011    01JUL11      0.1

Diane       7        2    2011    02JUL11      4.0

Diane       7        3    2011    03JUL11     11.0

Diane       7        4    2011    04JUL11     12.8

Diane       7        5    2011    05JUL11     16.5

Diane       7        6    2011    06JUL11      7.6

Diane       7        7    2011    07JUL11     16.8

Diane       7        8    2011    08JUL11      0.8

Diane       7        9    2011    09JUL11      6.9

Diane       7       10    2011    10JUL11     35.7

Ed          7        1    2011    01JUL11      3.3

Ed          7        2    2011    02JUL11      0.4

Ed          7        3    2011    03JUL11      9.2

Ed          7        4    2011    04JUL11      2.2

Ed          7        5    2011    05JUL11     10.1

Ed          7        6    2011    06JUL11      2.6

Ed          7        7    2011    07JUL11     15.5

Ed          7        8    2011    08JUL11      4.3

Ed          7        9    2011    09JUL11     14.7

Ed          7       10    2011    10JUL11      5.7

;

run;

ods listing;

options nocenter ;

proc print data=makeemp noobs;

  var empname month day year dateval hours;

run;

ods listing close;

** create a user-defined format for the traffic lighting;

proc format;

  value hrfmt low-5 = 'white'

              5 <-10 = 'cxdddddd'

              10<-25 = 'yellow'

              25<-high = 'pink';

run;

** use the format with HOURS, but use DATEVAL as an ACROSS item;

ods html file='c:\temp\hilite_hours.html' style=sasweb;

proc report data=makeemp nowd;

  title 'TrafficLighting with a User-Defined Format';

  columns empname hours,dateval hours=hourtot;

  define empname / group;

  define dateval / across 'Fake Hours Booked Each Day ';

  define hours / ' ' sum style(column)={background=hrfmt.};

  define hourtot / sum 'Total Hours';

run;

ods html close;

[need_some_help]

Cynthia, thanks for the answer) Didn't know about such possibilites in column statement as:

     hours,dateval

     hours=hourtot

- thought itshould be just listing of variables. And I still can't get used to formats - it's really very pointful.

But the thing is not only highlighting actually....

Last 2 rows (and last 2 columns) in my report present 'summary data' - the last one is summary working time for a day (for all employees) and the previous is the number of employees who worked more than one hour that day  (I didn't knew how to organize 'conditional count' in proc report - so I did it through SQL, and to avoid effecting this value on summary time I summed time in SQL too....). I know it's not a good way but I've made my report-table before proc report (in SQL) and then I used proc report to color cells, change value-formats or replace NULL-values. So I have fixed number of columns in my data set.

This report is going to be weekly, so I had an idea to 'shade' the days since current(report)-day to the end of current month. I guess that is not the 'best' idea but I've planned something like that:

%MACRO before_after(n);  /* n - №day*/
      define <'№day'n> / order missing noprint order=data;
      define n<№day> / computed <'№day'> missing order=data;
      compute n<№day> / character;

           %if &n lt %sysfunc(day(%sysfunc(today())))
           %then do;
                      %if agent ne <pre-last row>
                      %then do;
                                 %if <'№day'n> <30 %then n<№day>='-';  /*working time <30 seconds --> '-'*/
                                 %else do;
                                       n<№day>=strip(put(<'№day'n>,hhmm10.));
                                       CALL DEFINE(_COL_, "style", "STYLE=[BACKGROUND=ALICEBLUE]");
                                 end; 
                      end;
                      %else n<№day>= strip(put(<'№day'n>,5.));     /*number of employees worked >1h in №day*/
           end;

          %else do;
                      n<№day>=.;
                      CALL DEFINE(_COL_, "style", "STYLE=[BACKGROUND=grey]"); 
           end;  
 
           %if agent=<pre-last row> or agent=<last row>
           %then CALL DEFINE(_COL_, "style", "STYLE=[BACKGROUND=CXE1E8FB]");  /*colouring 2 last - summary rows*/

      endcomp; 
%MEND before_after;

%macro mproc;
proc report data=WORK.WORKTIME nowd;
column agent d1 '1'n ................d31 '31'n sum1 sum2;
define agent / group 'agent' missing;

%do i=1 %to %sysfunc(day(%sysfunc(INTNX(month,%sysfunc(today()),-1,e)))); /*end of current month*/
%before_after(&i);
%end; 

define sum1 / <.....>;
define sum2 / <.....>;
run;
quit;
%mend mproc;

%mproc;

Maybe it's hard to understand.... But could something like that be organized through proc report? (to delimitate 'past' and 'future' and to get 2 types of summary results - counting and summing - these are extra things, besides highlighting, I need in my report)

It'll be great if you manage to understand what I mean....)

[Cynthia_sas]

Hi:

  It sounds like you could benefit from taking our Report Writing 1 course https://support.sas.com/edu/schedules.html?id=284&ctry=US  or by getting Art Carpenter's book on PROC REPORT https://support.sas.com/pubscat/bookdetails.jsp?catid=1&pc=60966  or by reading some of the user group papers about PROC REPORT (Google works very nicely for this last recommendation).

           
  This construction in the COLUMN statement:
hours=hourtot
is called an alias. It is a way to get PROC REPORT let you use a report item more than one way on the report. So, I can use HOURS (the variable) nested underneath DATEVAL (HOURS,DATEVAL indicates nesting, with DATEVAL being the ACROSS report item)  for one usage on the report and then I can use HOURS, referenced by the alias HOURTOT, to give me the summary total of all hours for each report row (each employee).

                   
  The rule of thumb about "macro-izing" SAS code is that you should have a working program before you introduce macro coding into your program. I don't see that you have a working  Proc REPORT program. I have a hard time visualizing what it is you want. PROC REPORT could easily give you the summary columns and/or summary rows. This paper http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf talks about generating extra summary rows on pages 7-8 and the paper is good because it discusses other more advanced PROC REPORT topics.

                  
  One of the basics of PROC REPORT is that you have column headers and data cells in your report table. It is not clear to me what you want to highlight based on the "past" and "future". If your data only goes through July 15, for example, and you run the report on July 16, then you only have "past" on the report -- there is no "future". In your data example, you don't show enough data for anyone to see where the "future" columns would be. Since your data is unclear and your final results are unclear and since you don't have a working PROC REPORT program that shows a plain report without highlighting, I'm going to move past this discussion and onto the general issue of highlighting.

  It is entirely possible for PROC REPORT to highlight based on certain conditions. Some of your choices are to highlight based on either the number inside the data cell or to highlight based on the header/variable/item name of the column or to highlight either a row or a column based on the value of another item on the report (using a COMPUTE block and CALL DEFINE). The program below illustrates some highlighting methods, using 5 observations from SASHELP.CLASS.  Five separate types of formatting are illustrated, although #4 and #5 are very  similar. You can see all 5 outputs in the attached screenshot.  I did not show more advanced uses of highlighting using absolute column names with an ACROSS item (because your description implies that you are not using ACROSS on your report).             

                               
cynthia

               
ods listing close;
title; footnote;
ods html file='c:\temp\hilite_examp.html' style=sasweb;
           
proc format;
  value htfmt low-55 = 'white'
              55.0001-high = 'yellow';
run;
              
proc report data=sashelp.class(obs=5) nowd;
  title '1) highlight based on cell value';
  column name sex age height weight;
  define name / order;
  define sex / display;
  define age / display;
  define height / display
         style(column)={background=htfmt.};
  define weight / display;
run;
          
proc report data=sashelp.class(obs=5) nowd;
  title '2) highlight ROW based on gender';
  column name sex age height weight;
  define name / order;
  define sex / display;
  define age / display;
  define height / display ;
  define weight / display;
  compute sex;
    if sex = 'F' then
       call define(_ROW_,'style','style={background=pink}');
  endcomp;
run;
                
proc report data=sashelp.class(obs=5) nowd;
  title '3) highlight age, height and weight no matter what internal value is';
  column name sex age height weight;
  define name / order;
  define sex / display;
  define age / display;
  define height / display ;
  define weight / display;
  compute weight;
      ** in this context, _COL_ refers to WEIGHT;
      ** but HEIGHT and AGE have to be referenced by column name;
      call define(_COL_,'style','style={background=aliceblue}');
      call define('height','style','style={background=aliceblue}');
      call define('age','style','style={background=aliceblue}');
  endcomp;
run;
                 
%let highvar = AGE;
proc report data=sashelp.class(obs=5) nowd;
  title "4) highlight column based on macro variable value: &highvar";
  column name sex age height weight;
  define name / order;
  define sex / display;
  define age / display;
  define height / display ;
  define weight / display;
  compute &highvar;
      ** in this context, _COL_ refers to the value for whatever dataset;
      ** variable is referenced by macro variable &highvar;
      call define(_COL_,'style','style={background=aliceblue}');
  endcomp;
run;
                     
** now provide a list of variable names to highlight;
%let highvars = NAME AGE WEIGHT;
proc report data=sashelp.class(obs=5) nowd;
  title "5) highlight columns based on multiple var names in list (&HIGHVARS)";
  column name sex age height weight fakeitem;
  define name / order;
  define sex / display;
  define age / display;
  define height / display ;
  define weight / display;
  define fakeitem / computed noprint;
** use the FAKEITEM computed column in order to have a NOPRINT column;
** to reference multiple report items in one COMPUTE BLOCK location;
** with a DO loop inside the COMPUTE block.;
  compute fakeitem;
     fakeitem = 1;
     length hivar $32;
     hivar = 'xxx';
     stop = countw("&highvars");
     do i=1 to stop;
        hivar = scan("&highvars",i,' ');
        call define(hivar,'style','style={background=aliceblue}');
     end;
  endcomp;
run;
              
ods html close;
title; footnote;

[need_some_help]

Thank you very much for your answer (for trying to get the point of what I wrote). In my defense) I want to say that I'm googling all the issues I have - but there are so much documentation, so I get a little from here, a little from there - and sometimes a mess as a result(

I'll study out your answer attentively and try to apply it to my report.

May I ask one more time  - does proc report let to get conditional count for the column (e.g. count the number of values greater than 10) in summary row? with a compute block?

P.S.

Bad of me not to visual 'needed report' (so many words and no picture...).

That is somewhere about what I've planned (all days of month - ones which in the past filled with values, others (future ones) - shaded) - I wanted to use loop to shade 'future' columns...

[Cynthia_sas]

Hi:

  I am not on my computer right now, so I am unable to post code, but the short answer is yes, you can conditionally count values in a COMPUTE block. As I said, before you "macroize" any SAS program, you have to start with working SAS code -- so you need to come up with a working PROC REPORT program that produces the desired results WITHOUT trying any macro coding techniques.

cynthia

[need_some_help]

Cynthia, thanks a lot for your help. I'll try to remake (optimize) my report using all proc report facilities and when everything will work fine try to 'macro' it - hope there'll be no serious problems with that)

[Cynthia_sas · Accepted solution]

Hi:

  That should work for you (to develop your program first and then macroize it). As long as you are going to need a DATA step program or an SQL program to calculate some of your numbers or add an "extra" variable to the data so you can generate 2 summary rows at the bottom, my tendency would be to create the "count" variables in a DATA step program and then just sum them up in the COMPUTE block. Sometimes a hybrid of DATA step or SQL with PROC REPORT can get your data in the structure you want and can make your PROC REPORT code less verbose. Do remember that you can have a variable appear in the COLUMN statement, but you can "hide" that variable or a computed item using the NOPRINT option on the DEFINE statement.

  Still not at my computer, but note that AGE is used to decide whether to multiply HEIGHT or WEIGHT in the program below (not tested):

ods html file='c:\temp\calcvar.html' style=sasweb;

proc report data=sashelp.class nowd;

  column name age height weight calcvar;

  define name / order;

  define age / display;

  define height / sum  /* noprint  */;

  define weight / sum  /* noprint */;

  define calcvar / computed;

  compute calcvar;

       if age lt 13 then calcvar = height.sum *2;

       else if age ge 13 then calcvar = weight.sum*2;

  endcomp;

  rbreak after /summarize;

run;

ods html close;

You can run the code once, and note how the AGE item is used to determine the formula for creating CALCVAR from either HEIGHT or WEIGHT. Then, take the /* and */ comment delimiters out of the DEFINE statements and run the code a second time to note how HEIGHT and WEIGHT will be available to be used, but will not appear on the final report (if you use the NOPRINT option).

cynthia

[need_some_help]

This information is very useful - thank you. It will take some time for me to try to use all these things for my report features. I hope I can cope with it - I'll try to do my best) otherwise I'll ask for some help here again if you don't mind.