The SAS Output Delivery System and reporting techniques

re:proc report

Accepted Solution Solved
Reply
Regular Contributor
Posts: 228
Accepted Solution

re:proc report

Hi, I am trying to create a calendar similiar to the one that the code below generates. However, I want to change it so that instead of displaying the name of the Holiday, I want to color code the cell as I want to be able to have all 12 tables for each month on one page. How can I color code the cell (day) that is a holiday. Thanks in advance.

 

 

 

/* Create a data set containg one observation for each day in

the time period specified above. */

data calendar_&year(drop=fdoy);

format date mmddyy8. dow dow.;

do date=&beg_yr to &end_yr;

monthnum = month(date);

year = year(date);

dow = weekday(date);

monthname = strip(upcase(put(date,monname9.)))||' '||put(year(date),z4.);

dom = input(put(date,day2.), 2.);

week = intnx('week',date,0,'e');

/* Get holidays */

fdoy=mdy(1,1,&year);

length note $200;

note = ' ';

 

if date = mdy(1,1,&year)

then note = "New Year's Day";

if date = intnx('week.2',fdoy,(weekday(fdoy) ne 2)+2)

then note = "Martin Luther King Day";

 

else if date = intnx('week.2',intnx('month',fdoy,1),(weekday(intnx('month',fdoy,1)) ne 2)+2)

then note = "President's Day";

 

else if date = intnx('week.2',intnx('month',fdoy,4),(weekday(intnx('month',fdoy,4)) in (1,7))+4)

then note = "Memorial Day";

else if date = mdy(7,4,&year)

then note = "Independence Day";

else if date = intnx('week.2',intnx('month',fdoy,8),(weekday(intnx('month',fdoy,8)) ne 2))

then note = "Labor Day";

else if date = intnx('week.2',intnx('month',fdoy,9),(weekday(intnx('month',fdoy,9)) ne 2)+1)

then note = "Columbus Day";

else if date = mdy(11,11,&year)

then note = "Veteran's Day";

else if date = intnx('week.5',intnx('month',fdoy,10),(weekday(intnx('month',fdoy,10)) ne 5)+3)

then note = "Thanksgiving Day";

else if date = mdy(12,25,&year)

then note = "Christmas Day";

output;

end;

run;

/* Create notes for personal dates. */

data mydates;

length note $200;

date = mdy(1,20,&year); note = "Mom's B-day"; output;

date = mdy(5,25,&year); note = "^{style [foreground=blue] GRADUATION!}"; output;

date = mdy(8,21,&year); note = "^{style [foreground=red] First Day of School}"; output;

run;

/* Merge the two date data sets together. */

data calendar_&year;

merge calendar_&year mydates;

by date;

/* Append note text to the day of month value */

length dom_notes $200;

dom_notes = put(dom,8.) || "^n" || note;

run;

/* Create a week number. */

data calendar_&year;

set calendar_&year;

by monthnum week;

if first.monthnum then weeknum=0;

if first.week then weeknum+1;

run;

/* Transpose the data for the calendar with notes. */

proc transpose data=calendar_&year out=notes_calendar_&year;

by monthnum monthname weeknum;

id dow;

var dom_notes;

run;

/* Create a calendar with dates and notes. */

title "#byval(monthnum) &year";

proc report data=notes_calendar_&year center nowd

style(report)=[font=(Arial, 8pt)]

style(column)=[font=(Arial, 8pt) cellheight=1in cellwidth=1in]

style(header)=[font=(Arial, 8pt) font_weight=bold]

split='*';

by monthnum;

format monthnum nlstrmon.;

columns weeknum Sun Mon Tue Wed Thu Fri Sat;

define weeknum / order order=internal noprint;

run;


Accepted Solutions
Solution
‎12-22-2016 01:42 PM
SAS Super FREQ
Posts: 703

Re: re:proc report

add a _dummy column to your report that is at the very end of the column list.

 

this will allow you to access each individual cell and check the content, and the use the CALL DEFINE to color your cell.

 

See sample code below:

/* Create a calendar with dates and notes. */
title "#byval(monthnum) &year";

proc report data=notes_calendar_&year center nowd

  style(report)=[font=(Arial, 8pt)]

  style(column)=[font=(Arial, 8pt) cellheight=1in cellwidth=1in]

  style(header)=[font=(Arial, 8pt) font_weight=bold]

  split='*';
  by monthnum;

  /*  format monthnum nlstrmon.;*/
  columns weeknum Sun Mon Tue Wed Thu Fri Sat _dummy;
  define weeknum / order order=internal noprint;
  define _dummy / computed ;

  compute _dummy / char length=32;;
    array sugus{*} Sun Mon Tue Wed Thu Fri Sat;
    call define("sun", "style", "style={background=cx4dbfbc}");
     
    do i = 1 to dim(sugus);
      if length( strip(sugus{i}) ) > 4 then do;
        call define(vname(sugus{i}), "style", "style={background=cx52cc62}");
      end;
    end;
  endcomp;
run;

Bruno

View solution in original post


All Replies
Solution
‎12-22-2016 01:42 PM
SAS Super FREQ
Posts: 703

Re: re:proc report

add a _dummy column to your report that is at the very end of the column list.

 

this will allow you to access each individual cell and check the content, and the use the CALL DEFINE to color your cell.

 

See sample code below:

/* Create a calendar with dates and notes. */
title "#byval(monthnum) &year";

proc report data=notes_calendar_&year center nowd

  style(report)=[font=(Arial, 8pt)]

  style(column)=[font=(Arial, 8pt) cellheight=1in cellwidth=1in]

  style(header)=[font=(Arial, 8pt) font_weight=bold]

  split='*';
  by monthnum;

  /*  format monthnum nlstrmon.;*/
  columns weeknum Sun Mon Tue Wed Thu Fri Sat _dummy;
  define weeknum / order order=internal noprint;
  define _dummy / computed ;

  compute _dummy / char length=32;;
    array sugus{*} Sun Mon Tue Wed Thu Fri Sat;
    call define("sun", "style", "style={background=cx4dbfbc}");
     
    do i = 1 to dim(sugus);
      if length( strip(sugus{i}) ) > 4 then do;
        call define(vname(sugus{i}), "style", "style={background=cx52cc62}");
      end;
    end;
  endcomp;
run;

Bruno

Regular Contributor
Posts: 228

Re: re:proc report

Hi Bruno, I did try your suggestion and I am getting the following eror messages.

 

ERROR: Unable to load/initialize the function DIM

ERROR: Unable to load/initialize the function VNAME

 

SAS Super FREQ
Posts: 703

Re: re:proc report

[ Edited ]

I think, this is caused by the statement 

format monthnum nlstrmon.;

See what happens if you comment this out.

 

Bruno

Regular Contributor
Posts: 228

Re: re:proc report

Hi Bruno....Yes that was the problem...it works perfect....thanks a ton!!!

SAS Super FREQ
Posts: 703

Re: re:proc report

Hi

 

To avoid the error add the

format monthnum nlstrmon.;

statement to a DATA Step where the data is created then you get the desired result with no error

 

Bruno

Super User
Posts: 11,101

Re: re:proc report

You may want to investigate the SAS supplied function HOLIDAY to simplify some of this code.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 350 views
  • 0 likes
  • 3 in conversation