topic Re: plotting cumulative sums without prior calculation in Graphics Programming

plotting cumulative sums without prior calculation

WendyT — Thu, 15 Dec 2016 23:40:42 GMT

Hello SAS folks –

I will be making several series of graphics using cumulative sums at several different aggregation levels. I have been reading the SGPLOT documentation to see if there is anything I can use to generate the plots without having to actually calculate the cumulative totals beforehand, but no luck so far. If someone can point the way, I would be very grateful.

Most of the aggregations will be based on date (year, fiscal year, month, week), but we also have text categories as well, and possibly some combinations.

We are running 9.4M3 on a Windows server via remote login. We do have SAS/GRAPH, but I would prefer to use SG if possible, as I have quite a bit of existing code.

I made up a little example dataset, with date, person, and amount as variables.

The graphics must have “Date” on the X axis, and “Amount” on the Y axis. The code at the bottom gives a single graph with one line for each person. I also tried tinkering with FREQ and UNIVARIATE with no success on getting the axes correct. The closest I got was reversed axes.

Graph 1: cumulative sum by date:

X= 12/01/2016 Y=170 (10 from Lisa + 100 from John + 60 from Wendy)

X= 12/03/2016 Y= 390 (170 + 20 from Lisa + 200 from John)

X=12/10/2016 Y= 540 (390 + 150 from Steve)

etc.

Graph 2 (series): cumulative sum by date by person:

X= 12/01/2016 person=Lisa Y=10

X= 12/01/2016 person=Lisa Y=30 (10 + 20)

etc.

Thanks for any help you can give me!

Wendy T

data sample ;
input  Date  mmddyy10.  Person $ Amount ;
format date mmddyy10. ;
datalines ;
12/01/2016 Lisa   10
12/01/2016 John   100   
12/01/2016 Wendy  60   
12/03/2016 Lisa   20    
12/03/2016 John   200   
12/10/2016 Steve  150   
12/10/2016 Wendy  30    
12/11/2016 Lisa   80    
12/11/2016 John   100   
12/11/2016 Steve  80    
12/11/2016 Wendy  20    
12/12/2016 John   50    
12/15/2016 Steve  20    
12/15/2016 Wendy  20    
12/17/2016 Steve  100   
12/17/2016 Wendy  100   
12/28/2016 Steve  180   
12/28/2016 Steve  50    
12/29/2016 Lisa   50    
12/30/2016 Wendy  20    
;
run ;


%let OUTDIR= YourDirectory ;

ods listing close ;
ods graphics on /  reset=index imagename="graph" height=480px width=1280px imagemap=on ;

ods html newfile=none headtext='<style>p,hr {display:none} </style>' 
    path="&OUTDIR\" (url=none)
    body="body.htm" contents="contents.htm" frame="frame.html" ;

proc sgplot data=sample ;
  series x=date y=amount / markers group=person ;
run ;

ods html close ;
ods listing ;

Re: plotting cumulative sums without prior calculation

ballardw — Fri, 16 Dec 2016 00:09:32 GMT

You might try CDFPLOT in Proc Unvariate as a start.

I am not sure what a cumulative plot over a categorical like name would mean. There is no actual meaning to the order of names.

Re: plotting cumulative sums without prior calculation

Rick_SAS — Fri, 16 Dec 2016 13:01:11 GMT

I'm mostly guessing, but are you trying to plot the cumulative amounts for each person by date? If so, then this is a form of a spaghetti plot.

You can use a DATA step to form the cumulative sum, then use the SGPLOT code that you provided to plot the series for each person:

proc sort data=sample;
by Person date;
run;

data cusum;
set sample;
by person;
if first.Person then cusum = 0;
cusum + Amount;
run;

proc sgplot data=cusum;
label cusum = "Cumulative Amount";
  series x=date y=cusum / markers group=person ;
run ;

Re: plotting cumulative sums without prior calculation

WendyT — Fri, 16 Dec 2016 16:49:45 GMT

ballardw and Rick_SAS-

Thanks for the code, and I'll take a look at UNIVARIATE and the spaghetti plots.

Sorry to be unclear... I oversimplified.

What I'm working on at the moment is fisheries data: pounds of fish caught, price paid for the fish, fishermen and numbers of boats fishing, net size data, etc. etc. etc., and just to make it more challenging, I have multiple lakes with different fishing seasons over both calendar and fiscal years.

My hope was to be able to write a macro for general plots of a type, then pass in appropriate BY variables, time groupings via formats, etc. I've done similar things with water quality data with a macro that runs PROC MEANS, outputs to a dataset, then plots from there.

However, my sticky bit has been cumulative catch and cost data, and catch per unit effort, as the calculations change based on the groupings. I was hoping that SGPLOT could handle the cumulative part, so I didn't have to do the extra sorts and data steps.

A few examples might be: Total fish caught by Lake by FY, Catch by fisherman by FY, Catch per unit effort by Lake, Catch per unit effort by fisherman by time (FY, year, month, week), etc. etc. etc.

Thanks so much for your help!

Wendy T.