Mmm, dogfood

Here at SAS, we eat our own dogfood*. Actually, that's an understatement -- it's better to say that we feast on it.

I've been using SAS 9.2 (released earlier this year) and SAS Enterprise Guide 4.2 (not yet released) for many months (years, actually) to accomplish several tasks, including to track our open defect count and closure rates. (At SAS, software bugs are tracked as "defects" using an application that we call DEFECTS. And guess what? That application is built on SAS.)

I have useful plot that shows me a three-way trend analysis of:

open defect count (how many bugs sitting open each week for the current development cycle)

rate of new defects opened (for each week, how many new bugs were reported)

rate of defects resolved (for each week, how many bugs were fixed or otherwise resolved)

I want to show you this graph, because I built it using the new capabilities in the SGPLOT procedure. But I don't want to show you our defects data. I'm proud of our work, but I'm not about to air our dirty laundry. Instead, I came up with another example that uses the same general plot format, but relies on historical stock data that anybody can access. Here's a picture of the chart.

This example:

uses the FILENAME URL access method to pull financial data from Yahoo!

massages the data for Dow Jones Industrial Average and 3 ticker symbols (my arbitrary picks) into a single data set.

uses the SGPLOT procedure to display the trends of the ticker symbols (looks like line plots), with the close value of the DJIA in the backround as a needle plot (looks like a bar chart).

commits a data visualization sin by setting the baseline of the Y axis (Dow Jones close value) to 10,000, rather than 0. I did this just to emphasize the differences among the days. I don't do this in my defect reports, but I want to point out that I knew I was doing this in the example, and that I know it's usually a no-no.

Here is the code. It requires SAS 9.2 to run it.

%macro readstockdata(ticker,isIndex);
%let monnum = %eval(%sysfunc(month(%sysfunc(today())))-1);
%let daynum = %sysfunc(day(%sysfunc(today())));
%let yearnum = %sysfunc(year(%sysfunc(today())));
%let ticker=%sysfunc(upcase(&ticker));
%if &isIndex = Y %then %let prefix=%nrstr(%5E); %else %let prefix=;
/** add a proxyserver= option here if you need to run this from behind a firewall **/
filename &ticker url "http://ichart.finance.yahoo.com/table.csv?s=&prefix&ticker%nrstr(&d)=&monnum%nrstr(&e)=&daynum%nrstr(&f)=&yearnum%nrstr(&g)=d%nrstr(&a)=9%nrstr(&b)=1%nrstr(&c)=1928%nrstr(&ignore)=.csv";

data &ticker;
attrib Date length=8 format=YYMMDD10. informat=YYMMDD10.
High Low Close Volume adjclose length=8 format=best9.;
infile &ticker dlm=',' missover firstobs=2 dsd ;
/** the ?? modifier suppresses error/notes when reading invalid data **/
input
Date : ?? YYMMDD10.
Open : ?? COMMA6.
High : ?? COMMA6.
Low : ?? COMMA6.
Close : ?? COMMA6.
Volume : ?? BEST9.
adjclose : ?? COMMA5. ;
run;
%mend;

%readstockdata(gm,N);
%readstockdata(msft,N);
%readstockdata(dji,Y);
%readstockdata(yhoo,N);

/** merge the stock prices and index data so that **/
/** we have a single data source to plot **/
data aggregate;
merge dji (rename=(adjclose=dji) keep=date adjclose)
gm(rename=(adjclose=gm) keep=date adjclose)
msft(rename=(adjclose=msft) keep=date adjclose)
yhoo(rename=(adjclose=yhoo) keep=date adjclose);
by descending date;
format dji comma10.;
label dji="DJIA daily close";
run;

ods graphics /width=800px height=300px ;
title "Historical stock data";
footnote "As of %SYSFUNC(DATE(), NLDATE20.) at %SYSFUNC(TIME(), TIMEAMPM8.)";
/** trim the data for the YTD using a WHERE option **/
proc sgplot data=aggregate(where=(date>"01JAN2008"D));
/** specify type=discrete and fitpolicy so that date values **/
/** don't show gaps for the weekend dates **/
xaxis type=discrete fitpolicy=rotatethin display=(novalues)
label="Daily close prices (01JAN2008 thru %sysfunc(putn(%sysfunc(today()),date9.))";
y2axis min=0 label="Adjusted Close Price" ;
needle x=date y=dji / transparency=0.7 lineattrs=(color=gray thickness=5px) baseline=10000;
series x=date y=gm / lineattrs=(color=red) y2axis legendlabel="GM" ;
series x=date y=msft / lineattrs=(color=green) y2axis legendlabel="MSFT" ;
series x=date y=yhoo / lineattrs=(color=purple) y2axis legendlabel="YAHOO" ;
run;

* "Kibbles and Bits" would be a great name for software-based dogfood. Just add data and it makes its own gravy.