BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
VinodKavery
Calcite | Level 5

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
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User
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

View solution in original post

2 REPLIES 2
Kurt_Bremser
Super User
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

VinodKavery
Calcite | Level 5
Thank you sir

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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