Hi, I want to perform a certain operation on rows of a dataset in the following way: I have a dataset lIke this one:
Name ID code value
A 1 x1 2
A 3 x2 22
A 5 x3 31
B 1 x1 4
B 3 x2 23
B 5 x3 1
C 1 x1 4
C 3 x2 43
C 5 x3 2
What I need is, add the first two rows and then, the result multiply by the third one but for each name group. In this case a dataset like this one
Name Result
A 774
B 27
C 94
In the real dataset there are 56 name groups but it is not a fixed number, it can vary depending on some previous calculations. Anyone knows how can I do this? Thanks in advance.
Hi and welcome to the SAS Communities 🙂
I would do something like this
data have;
input Name $ ID code $ value;
cards;
A 1 x1 2
A 3 x2 22
A 5 x3 31
B 1 x1 4
B 3 x2 23
B 5 x3 1
C 1 x1 4
C 3 x2 43
C 5 x3 2
;
data want(keep=Name Result);
do i=1 by 1 until (last.Name);
set have;
by Name;
if i=1 then Result=value;
else if i=2 then Result=Result+value;
else if i=3 then Result=Result*value;
end;
run;
data have;
input Name $ ID code $ value;
cards;
A 1 x1 2
A 3 x2 22
A 5 x3 31
B 1 x1 4
B 3 x2 23
B 5 x3 1
;
data want;
set have;
by name id;
retain cnt;
if first.name then cnt=.;
cnt+value;
cnt2=lag(cnt);
if first.name then cnt2=.;
if last.name then cnt=cnt2*value;
if last.name;
drop cnt2;
run;
Thank you!! This was also a solution but for some technical reasons I applied the other one.
Hi and welcome to the SAS Communities 🙂
I would do something like this
data have;
input Name $ ID code $ value;
cards;
A 1 x1 2
A 3 x2 22
A 5 x3 31
B 1 x1 4
B 3 x2 23
B 5 x3 1
C 1 x1 4
C 3 x2 43
C 5 x3 2
;
data want(keep=Name Result);
do i=1 by 1 until (last.Name);
set have;
by Name;
if i=1 then Result=value;
else if i=2 then Result=Result+value;
else if i=3 then Result=Result*value;
end;
run;
Thanks!!!! Both were good solutions but for technical reasons I applied this one.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.