topic Re: how to transform monthly data to cumulative monthly data in SAS Procedures

how to transform monthly data to cumulative monthly data

coba — Wed, 17 Dec 2008 14:55:43 GMT

I have a data set based on month and I would like to make a cumulative month data set. Could someone help me with the program code?
Thanks

Re: how to transform monthly data to cumulative monthly data

Patrick — Wed, 17 Dec 2008 21:59:22 GMT

Hi coba
Have a look what Proc Summary/Proc Means can do for you.
To get a more specific answer you have to provide much more information: What do you have, what do you want - and best some code creating some sample data.
Cheers, Patrick

Re: how to transform monthly data to cumulative monthly data

coba — Thu, 18 Dec 2008 09:08:10 GMT

sorry that is true, should have be clearer.
I have a volumes series link to a month series i.e.
date volume
01-Jan-08 5000
02-Feb-08 10000
05-Mar-08 15000
06-Apr-08 5000
08-May-08 4000
09-Jun-08 1000
11-Jul-08 100
12-Aug-08 2000
out of these series I would like to make the cumulative volume. There must be something with the retain function, not quite sure. Hope you will be able to help

Re: how to transform monthly data to cumulative monthly data

coba — Thu, 18 Dec 2008 15:52:01 GMT

I manage to make cumulative series, but how do I manage to have by year and by type?
data work.cum;
input date date7. volume type $;
datalines;
01jan07 2000 a
01feb07 1000 a
01jan07 2000 b
01feb07 1000 b
01jan07 2000 c
01feb07 1000 c
01jan08 2000 a
01feb08 1000 a
01jan08 2000 b
01feb08 1000 b
01jan08 2000 c
01feb08 1000 c
;

data work.cum2;
set work.cum;
format date date7.;
retain cumvol 0;
cumvol = sum( cumvol, volume, type);
run;

Re: how to transform monthly data to cumulative monthly data

Cynthia_sas — Thu, 18 Dec 2008 16:06:40 GMT

Hi:
Investigate the use of the BY statement and the FIRST.byvar and LAST.byvar variables.

For example, this output:
[pre]
Obs Name Sex Age Height Weight allheight genderheight

1 Alice F 13 56.5 84.0 56.5 56.5
2 Barbara F 13 65.3 98.0 121.8 121.8
3 Carol F 14 62.8 102.5 184.6 184.6
4 Jane F 12 59.8 84.5 244.4 244.4
5 Janet F 15 62.5 112.5 306.9 306.9
6 Joyce F 11 51.3 50.5 358.2 358.2
7 Judy F 14 64.3 90.0 422.5 422.5
8 Louise F 12 56.3 77.0 478.8 478.8
9 Mary F 15 66.5 112.0 545.3 545.3
--------------------------------------------------------------------------------------------------
10 Alfred M 14 69.0 112.5 614.3 69.0
11 Henry M 14 63.5 102.5 677.8 132.5
12 James M 12 57.3 83.0 735.1 189.8
13 Jeffrey M 13 62.5 84.0 797.6 252.3
14 John M 12 59.0 99.5 856.6 311.3
15 Philip M 16 72.0 150.0 928.6 383.3
16 Robert M 12 64.8 128.0 993.4 448.1
17 Ronald M 15 67.0 133.0 1060.4 515.1
18 Thomas M 11 57.5 85.0 1117.9 572.6
19 William M 15 66.5 112.0 1184.4 639.1

[/pre]

created from SASHELP.CLASS, shows a "cumulative height" for all students and a cumulative "gender" height for the females and males.
(I put a dividing line between the 2 groups of observations, so you could see that the GENDERHEIGHT variable is reset to 0 when the first M observation is encountered, while the ALLHEIGHT variable keeps accumulating.

The code that produced this output is below.

cynthia
[pre]
proc sort data=sashelp.class out=class;
by sex name;
run;

data cumclass;
set class;
by sex;
retain allheight genderheight;
if first.sex then genderheight=0;
allheight + height;
genderheight + height;
output;
run;

options nocenter nodate nonumber;
proc print data=cumclass;
run;
[/pre]

Re: how to transform monthly data to cumulative monthly data

coba — Sun, 21 Dec 2008 16:50:10 GMT

how do you manage when you have more variables to classify, following you example, age, color of eyes?
would you

if first.sex then genderheight=0; allheight + height;

add else and use the other variables?

Re: how to transform monthly data to cumulative monthly data

deleted_user — Mon, 22 Dec 2008 16:25:47 GMT

you can derive the cumulative picture across more than one cross-classing, but would they consolidate ?

imagine the data consolidated by
age
sex

I would recommend making your time-based data complete in the granularity you need (monthly). So you would have a data cell for every age/sex/month
Then that can be accumulated where-ever needed.

I can imagine many ways of accumulation within some, or all, classings.
In a PROC TABULATE step, you might show a column for each month of the period, showing how the statistics "accumulate" over time. [pre]data birthdays ;
set sashelp.class ;
* invent a date of birth based on age and randomly in year;
dob = today() - age*365.25 - 12*30*ranuni(2) ; * testing data ;
bmonth = month( dob );
run;

proc summary nway ;
class sex age bmonth ;
var height ;
output sum= ;
run;

data completed ;
s_a_ht = 0 ;
do until( last.age ) ;
set ;
by sex age ;
array cumHt(12) ;
cumHt( bmonth) = height ;
s_a_ht + height ;
end ;
height = s_a_ht ;
* do acumulation over the year ;
do bmonth= 2 to 12 ;
cumHt(bmonth)= sum( cumHt(bmonth), cumht( bmonth-1), 0 );
end ;
keep sex age cumHt1-cumHt12 height ;
run;

proc tabulate format= 5. ;
class sex age ;
var cumHt1-cumHt12 height;
table (sex*(age all) all)
, sum= 'accumulative totals of Height over year'
* (cumHt1 cumHt2 cumHt3 cumHt4 cumHt5 cumHt6
cumHt7 cumHt8 cumHt9 cumHt10 cumHt11 cumHt12 )
sum= 'total'*height * f=6.
/ rts= 10 ;
label cumHt1 = 'Jan' cumHt2 = 'Feb' cumHt3 = 'Mar' cumHt4 = 'Apr'
cumHt5 = 'May' cumHt6 = 'Jun' cumHt7 = 'Jul' cumHt8 = 'Aug'
cumHt9 = 'Sep' cumHt10= 'Oct' cumHt11= 'Nov' cumHt12= 'Dec';
run;[/pre]
tested in SAS9.1.3 on win-XP, I got the result[pre] The SAS System 12:14 Monday, December 22, 2008

.---------------------------------------------------------------------------------------.
| | accumulative totals of Height over year |total |
| |-----------------------------------------------------------------------+------|
| | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |Height|
|--------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+------|
|Sex|Age | | | | | | | | | | | | | |
|---+----| | | | | | | | | | | | | |
|F |11 | .| 0| 0| 0| 51| 51| 51| 51| 51| 51| 51| 51| 51|
| |----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+------|
| |12 | .| 0| 0| 56| 56| 56| 56| 56| 56| 56| 116| 116| 116|
| |----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+------|
| |13 | 57| 57| 122| 122| 122| 122| 122| 122| 122| 122| 122| 122| 122|
| |----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+------|
| |14 | .| 0| 0| 0| 0| 63| 63| 63| 63| 63| 63| 127| 127|
| |----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+------|
| |15 | .| 0| 0| 0| 0| 0| 0| 0| 0| 0| 129| 129| 129|
| |----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+------|
| |All | 57| 57| 122| 178| 229| 292| 292| 292| 292| 292| 481| 545| 545|
|---+----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+------|
|M |Age | | | | | | | | | | | | | |
| |----| | | | | | | | | | | | | |
| |11 | .| 0| 0| 0| 0| 0| 0| 58| 58| 58| 58| 58| 58|
| |----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+------|
| |12 | 57| 57| 57| 57| 122| 122| 122| 122| 122| 122| 181| 181| 181|
| |----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+------|
| |13 | .| 0| 0| 0| 0| 0| 0| 0| 0| 0| 63| 63| 63|
| |----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+------|
| |14 | .| 64| 64| 64| 64| 64| 64| 133| 133| 133| 133| 133| 133|
| |----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+------|
| |15 | 67| 67| 67| 67| 67| 134| 134| 134| 134| 134| 134| 134| 134|
| |----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+------|
| |16 | 72| 72| 72| 72| 72| 72| 72| 72| 72| 72| 72| 72| 72|
| |----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+------|
| |All | 196| 259| 259| 259| 324| 391| 391| 518| 518| 518| 639| 639| 639|
|--------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+------|
|All | 252| 316| 381| 437| 554| 683| 683| 810| 810| 810| 1120| 1184| 1184|
'---------------------------------------------------------------------------------------'[/pre]

hey

is there a set of line-drawing characters to make that look better, or use SAS Monospace ?

PeterC