topic Adding rows cummulatively into new variable in SAS Programming

Adding rows cummulatively into new variable

PrinceAde — Thu, 07 Sep 2023 08:41:56 GMT

Hi.
Please I need help getting these data set.
There are two SAS data sets.
“Have” contains four variables t1-t4 and site. I'm trying to add these variables across each site into a new variable l1-l3 in the data set “want”.
How can I do this?

Have;
Site t1 t2 t3 t4
B 1 4 5 3
C 1 2 3 3
D 2 1 5 1
E 1 4 5 3

Want;

Site t1 t2 t3 t4 l1 l2 l3
B 1 4 5 3 5 10 13
C 1 2 3 3 3 6 9
D 2 1 5 1 3 8 9
E 1 4 5 3 5 10 13

The code I have is not giving the desired output;
data want;
set have;
by Site;
array t[4] t1-t4;
array l[3] l1-l3;
retain l1-l3;

if first.Site then do;
l1 = t[1];
l2 = t[2];
l3 = t[3];
end;
else do;
l1 + t[1];
l2 + t[2];
l3 + t[3];
end;
run;

Re: Adding rows cummulatively into new variable

PeterClemmensen — Thu, 07 Sep 2023 09:07:37 GMT

Try this

data have;
input Site $ t1 - t4;
datalines;
B 1 4 5 3
C 1 2 3 3
D 2 1 5 1
E 1 4 5 3
;

data want(drop = i);
   set have;
   by Site;
   array t[4] t1-t4;
   array l[3] l1-l3;

   l[1] = sum(t[1], t[2]);

   do i = 2 to dim(l);
      l[i] = sum(l[i-1], t[i+1]);
   end;
run;

Result:

Site t1 t2 t3 t4 l1 l2  l3
B    1  4  5  3  5  10  13
C    1  2  3  3  3  6   9
D    2  1  5  1  3  8   9
E    1  4  5  3  5  10  13

Re: Adding rows cummulatively into new variable

Amir — Thu, 07 Sep 2023 09:25:05 GMT

Hi,

Based on the data provided, I removed the by processing as each site was unique:

data want2;
  set have;

  array t[4] t1-t4;
  array l[3] l1-l3;
  
  l1 = sum(t[1],t[2]);
  l2 = sum(l1,t[3]);
  l3 = sum(l2,t[4]);
run;

Thanks & kind regards,

Amir.

Re: Adding rows cummulatively into new variable

PeterClemmensen — Thu, 07 Sep 2023 09:26:18 GMT

@Amir , sure thing, I left it there since I took your own code as the base.

Glad you found your solution.

Re: Adding rows cummulatively into new variable

Amir — Thu, 07 Sep 2023 09:29:38 GMT

Hi @PeterClemmensen,

Thanks. My post was a reply to the OP, which is perhaps who you thought I was!

Thanks kind regards,

Amir.

Re: Adding rows cummulatively into new variable

PeterClemmensen — Thu, 07 Sep 2023 10:02:54 GMT

Ah sorry. Missed that 😄

Re: Adding rows cummulatively into new variable

PrinceAde — Thu, 07 Sep 2023 10:16:06 GMT

@PeterClemmensen

Thank you very much. it works perfectly.

Re: Adding rows cummulatively into new variable

PrinceAde — Thu, 07 Sep 2023 10:18:40 GMT

@Amir This works perfectly with my data.

Thank you!