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
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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.