Help using Base SAS procedures

how to transform monthly data to cumulative monthly data

Reply
Contributor
Posts: 32

how to transform monthly data to cumulative monthly data

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
Respected Advisor
Posts: 3,887

Re: how to transform monthly data to cumulative monthly data

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
Contributor
Posts: 32

Re: how to transform monthly data to cumulative monthly data

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
Contributor
Posts: 32

Re: how to transform monthly data to cumulative monthly data

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;
SAS Super FREQ
Posts: 8,743

Re: how to transform monthly data to cumulative monthly data

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]
Contributor
Posts: 32

Re: how to transform monthly data to cumulative monthly data

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?
N/A
Posts: 0

Re: how to transform monthly data to cumulative monthly data

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
Ask a Question
Discussion stats
  • 6 replies
  • 137 views
  • 0 likes
  • 4 in conversation