topic Re: cumulative mean in SAS Programming

cumulative mean

wkossack — Mon, 12 Jul 2010 16:22:22 GMT

I have a monthly dataset going back a couple years. is there a way in SQL I can
compute an average for each row going back to the first row.

values
month value
jan 1
Feb 2
Mar 3
Apr 4

For example, average for first row would be '1' average for second row would be 1.5 and 3rd would be 3

Re: cumulative mean

deleted_user — Mon, 12 Jul 2010 20:05:34 GMT

not sure about sql but you can do it in data step using retain function..

Re: cumulative mean

SASKiwi — Mon, 12 Jul 2010 21:30:07 GMT

Try this:

data average;
set xxx;
total + value;
average = total/_n_;
run;

Re: cumulative mean

wkossack — Tue, 13 Jul 2010 15:00:31 GMT

how about in SQL?

Re: cumulative mean

SASKiwi — Tue, 13 Jul 2010 23:37:29 GMT

Your task is far easier to do in a DATA step because you can process one row at a time and accumulate results as you go. This technique is impossible in simple SQL because it does not process row by row. An SQL solution would require multiple queries and probably joining as well. Why not just use the easy DATA step way?!

Re: cumulative mean

wkossack — Wed, 14 Jul 2010 15:50:16 GMT

what I don't understand is that when I started working on this I found SQL functions (I think it was in oracle) that would compute cumulative means etc but when I tried them in SAS they did not work

Re: cumulative mean

Paige — Wed, 14 Jul 2010 17:40:27 GMT

Not everything is identical in SQL implemented for Oracle and SQL implemented in SAS. There are differences!

Re: cumulative mean

Jessica98 — Thu, 22 May 2014 09:19:43 GMT

Hi,

I have the slightly different problem. I want to calculate the cumulative mean but want to insert classification by an identification number. Take a look at the sample below. The first table show what i get when i run the data stpe by SASkiwin above. But i want tell SAS that it has to repeat the same thing for difference classes within the same dataset. The BY variable did not help! Any suggestions guys? I am relatively new to SAS!

time_period	ID	income	total	average
1	1	50	50	50
2	1	43	93	71.5
3	1	12	105	82.66667
4	1	34	139	96.75
1	2	60	199	117.2
2	2	21	220	134.3333
3	2	34	254	151.4286
4	2	12	266	165.75

this is what I want (below)

time_period	ID	income	total	average
1	1	50	50	50
2	1	43	93	71.5
3	1	12	105	82.66667
4	1	34	139	96.75
1	2	60	34	34
2	2	21	81	57.5
3	2	34	115	76.66667
4	2	12	127	89.25

Jessica

Re: cumulative mean

RW9 — Thu, 22 May 2014 09:44:04 GMT

Hi,

In answer to your initial post, it should be real easy to get the cumulative average:

data have;
attrib month format=$20. month_id value format=best.;
infile datalines delimiter=",";
input month $ month_id value;
datalines;
jan,1,45
feb,2,32
mar,3,67
apr,4,34
;
run;

proc sql;
create table WANT as
select A.*,
(select SUM(VALUE) from WORK.HAVE where MONTH_ID <= A.MONTH_ID) / (select COUNT(MONTH) from WORK.HAVE where MONTH_ID <= A.MONTH_ID) as CUMULATIVE_AVG
from HAVE A;
quit;

In answer to your latest post (which I just posted on the other post), with the groupings:

data have;
attrib id time_period income format=best.;
infile datalines delimiter=",";
input id time_period income;
datalines;
1,1,45
1,2,32
1,3,67
1,4,34
2,1,23
2,2,89
2,3,78
2,4,10
;
run;

proc sql;
create table WANT as
select A.*,
          (select SUM(INCOME) from WORK.HAVE where ID=A.ID and TIME_PERIOD <= A.TIME_PERIOD) as TOTAL,
          CALCULATED TOTAL / (select COUNT(ID) from WORK.HAVE where ID=A.ID and TIME_PERIOD <= A.TIME_PERIOD) as CUMULATIVE_AVG
from    HAVE A;
quit;