DATA Step, Macro, Functions and more

Summation of the quarterly observations

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Summation of the quarterly observations

Hi there, I have a SAS Dataset consisting of the Data as mentioned below. These are quarterly figures and do not have an ID column. The number of observations can vary and it need not be 31. I would like to sum the last 4 obs from the bottom of predicted column and skip those 4 & again sum the next last 4 observations.

It is like year2 = 1500 + 1500 +1500 +1500 = 6000 and year1=1000 +1000 +1000 +1000 = 4000.

Once this is done, calculate from Actual column .year0 = 900+ 900+900+900 =3600. Post this calculate the growth percentage numbers i.e., year1_gr = (year1/year0-1)*100 and  year2_gr = (year2/year1-1)*100

 

Could anyone please help me with the SAS code in doing it?

 

ObsActualPredicted
1         1,069         1,293
2         1,320         1,274
3         1,284         1,307
4         1,318         1,473
5         1,351         1,241
6         1,371         1,263
7         1,123         1,347
8         1,060         1,357
9         1,366         1,420
10         1,080         1,444
11         1,259         1,393
12         1,467         1,239
13         1,034         1,486
14         1,290         1,114
15         1,367         1,327
16         1,391         1,325
17         1,218         1,353
18         1,364         1,188
19         1,372         1,116
20         900         1,127
21         900         1,383
22         900         1,100
23         900         1,134
24Missing         1,000
25 Missing         1,000
26Missing         1,000
27Missing         1,000
28Missing         1,500
29Missing         1,500
30Missing         1,500
31Missing         1,500

Accepted Solutions
Solution
‎03-21-2017 08:40 AM
Super User
Posts: 6,938

Re: Summation of the quarterly observations

data inter;
set have;
no = _n_;
run;

proc sort data=inter;
by descending no;
run;

data want (keep=year2 year1 year0);
array year {3} year2-year0;
do j = 1 to 3;
  year{j} = 0;
  do i = 1 to 4;
    set inter;
    if j ne 3
    then year{j} = year{j} + predicted;
    else year{j} = year{j} + actual;
  end;
end;
output;
stop;
run;

First reverse the order by observation number

Then extract the data in groups of 4, and add up in an array

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎03-21-2017 08:40 AM
Super User
Posts: 6,938

Re: Summation of the quarterly observations

data inter;
set have;
no = _n_;
run;

proc sort data=inter;
by descending no;
run;

data want (keep=year2 year1 year0);
array year {3} year2-year0;
do j = 1 to 3;
  year{j} = 0;
  do i = 1 to 4;
    set inter;
    if j ne 3
    then year{j} = year{j} + predicted;
    else year{j} = year{j} + actual;
  end;
end;
output;
stop;
run;

First reverse the order by observation number

Then extract the data in groups of 4, and add up in an array

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

Re: Summation of the quarterly observations

Thank you sir
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 94 views
  • 0 likes
  • 2 in conversation