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?
Obs | Actual | Predicted |
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 |
24 | Missing | 1,000 |
25 | Missing | 1,000 |
26 | Missing | 1,000 |
27 | Missing | 1,000 |
28 | Missing | 1,500 |
29 | Missing | 1,500 |
30 | Missing | 1,500 |
31 | Missing | 1,500 |
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
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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.