topic Row operations grouped by name in New SAS User

Row operations grouped by name

juan_cifuentes — Mon, 13 May 2019 05:30:10 GMT

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.

Re: Row operations grouped by name

Jagadishkatam — Mon, 13 May 2019 08:13:00 GMT

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;

Re: Row operations grouped by name

PeterClemmensen — Mon, 13 May 2019 07:41:49 GMT

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;

Re: Row operations grouped by name

juan_cifuentes — Tue, 14 May 2019 21:54:28 GMT

Thanks!!!! Both were good solutions but for technical reasons I applied this one.

Re: Row operations grouped by name

juan_cifuentes — Tue, 14 May 2019 21:55:24 GMT

Thank you!! This was also a solution but for some technical reasons I applied the other one.