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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.