SAS: plotting dates excluding weekends using PROC SGPLOT

Question

I'm running SAS 9.3, and I'm trying to create a plot using PROC SGPLOT that (1) shows only dates contained in my dataset as consecutive dates (ie, excluding weekends and holidays), while (2) displaying usable information on the XAXIS (eg, the first day of a month). I can do both of these separately, but I can't figure out a way both of these together. Specifically, the VALUE and TICKVALUEFORMAT options seem to be disabled when I use the DISCRETE option (I don't see any discussion of this in the documentation).

So I have 2 questions:

(1) Is there a way to solve this interaction between the DISCRETE option and VALUES/TICKVALUES?

(2) Is there a better way to do what I'm trying to do?

(The PERFECT thing would to plot X and Y variables, and use a third variable as labels for the x-axis, but I can't find a way to do this.)

Here is some sample code that illustrates this; if excluding weekends/holidays, then the code should plot a straight line (I've attached plots generated below):

*Create Dataset of Weekdays, dropping some holidays;
data weekdays (where=(weekday~=1 and weekday~=7 and date~="01JAN1960"d));
format date date9.;
do i=0 to 100;
  date=i;
  weekday=weekday(date);
  year=year(date);
  month=year*100+month(date);
  output;
end;
drop i; 
run;

*Create line and data label;
data weekdays;
  set weekdays;
  line=_n_;
  format xlab $12.;
  by month;
  if first.month then xlab=put(date,monyy7.);
run;

The following plots the dates as DISCRETE points: it plots the datapoints correctly, but the VALUES and TICKVALUEFORMAT commands have no impact:

*Plot dates as discrete -- correct points, VALUES and TICKVALUEFORMAT have no effect;
proc sgplot data=weekdays;
  series x=date y=line;
  scatter x=date y=line;
  xaxis type=discrete DISCRETEORDER=DATA values=(3 31 60 91) TICKVALUEFORMAT=monyy7.;
run;

Here is the first plot: correct points, wrong axis

The following code is the same, but removes the DISCRETE option, and therefore it gets plotted as a date axis, which obviously includes weekends, but the VALUE and TICKVALUEFORMAT options work:

*Plot all dates as dates -- incorrect points, but VALUE and TICKVALUEFORMAT work;
proc sgplot data=weekdays;
  series x=date y=line;
  scatter x=date y=line;
  xaxis values=(3 31 60 91) TICKVALUEFORMAT=monyy7.;
run;

Here is the second plot: wrong points, but working axis

Any suggestions would be warmly welcomed! Thank you!

Joe Joe · Accepted Answer · 2017-09-29T17:35:51

The issue here is that when you go to discrete, SAS no longer really thinks of the variable as being anything other than text. You can see this if you use the TMPLOUT option in the two examples: in the first, the values are quoted, because thye're basically treated as character.

One way to work around this is to use the x2axis, and a secondary variable.

Here, in the create step, I add a counter that will count linearly - what you're doing. So 1,2,3,4,5.

*Create Dataset of Weekdays, dropping some holidays;
data weekdays ;
format date date9.;
do i=0 to 100;
  date=i;
  weekday=weekday(date);
  year=year(date);
  month=year*100+month(date);
  if (weekday~=1 and weekday~=7 and date~="01JAN1960"d) then do;
    counter+1;
    output;
  end;
end;
run;

*Create line and data label;
data weekdays;
  set weekdays;
  line=_n_;
  format xlab $12.;
  by month;
  if first.month then xlab=put(date,monyy7.);
run;

Now, I add a "dummy" plot that is intentionally not displayed (white and transparent) but tells SAS what to use for the XAxis. That I just use a normal FORMAT on to get to the right value labels. Then I tell the other plots to use X2AXIS which means they can make up their own axis (and remove the DISPLAY=NONE if you want to see what that would look like) but still appear the same.

*Plot dates as discrete -- correct points, VALUES and TICKVALUEFORMAT have no effect;
proc sgplot data=weekdays tmplout="c:\temp\test_sgplot2.sas";
    scatter x=date y=line/markeropts=(color=white) transparency=1;
    format date monyy7.;
  series x=counter y=line/x2axis;
  scatter x=counter y=line/x2axis;
  xaxis values=(3 31 60 91) type=discrete discreteorder=data TICKVALUEFORMAT=monyy7.;
  x2axis display=none;
run;

SAS: plotting dates excluding weekends using PROC SGPLOT

1 Answers