topic Re: how to sum a value in row 1 with row 2 in SAS Enterprise Guide

how to sum a value in row 1 with row 2

CasperH_ — Thu, 12 Mar 2015 09:53:21 GMT

I have a table with 600 observations (rows) with a numeric value for each row.

I want to create a new column (B) where B1 is the value in A1, B2 = A1+A2, B3 = A1+A2+A3 and so forth

Can this be done in Enterprise Guide? I have looked for it but could not find any solution.

I am not yet familiarized with SAS code but if anyone has the solútion in code I will try and use that but will be very useful if it can be done in EG1.

Regards

Casper

Re: how to sum a value in row 1 with row 2

Ksharp — Thu, 12 Mar 2015 12:42:55 GMT

B+A;

Re: how to sum a value in row 1 with row 2

RW9 — Thu, 12 Mar 2015 12:56:51 GMT

I would add that you probably want a retain on that:

have:

want:

A B

1 1

4 5

5 10

If so then:

data want;

set have;

retain b;

if _n_=1 then b=a;

else b=b+a;

run;

Re: how to sum a value in row 1 with row 2

data_null__ — Thu, 12 Mar 2015 13:39:13 GMT

You don't need RETAIN with SUM statement.

The variable on the left is initialized to 0 and retained.

Re: how to sum a value in row 1 with row 2

RW9 — Thu, 12 Mar 2015 13:53:56 GMT

Sorry, not sure I follow you. I was referring to the OP who put: " A1, B2 = A1+A2, B3 = A1+A2+A3 " so B in row 3 should be 1 + 4 + 5, so the value from the the last observation is added to the one on the current observation. These two want's give different results:

data have;

a=1; output;

a=4; output;

a=5; output;

run;

data want1;

set have;

b=sum(a,b);

run;

data want2;

set have;

retain b;

if _n_=1 then b=a;

else b=b+a;

run;

Re: how to sum a value in row 1 with row 2

art297 — Thu, 12 Mar 2015 14:00:04 GMT

RW9: I think DN was referring to the following form:

Correct but still more complex than needed:

data want;

set have;

if _n_=1 then b=a;

else b+a;

run;

Better (per data_null_'s subsequent post):

data want;

set have;

b+a;

run;

Re: how to sum a value in row 1 with row 2

RW9 — Thu, 12 Mar 2015 14:02:04 GMT

Ah, ok

Re: how to sum a value in row 1 with row 2

data_null__ — Thu, 12 Mar 2015 14:02:27 GMT

RTM Not SUM function SUM STATEMENT.

data have;
    a=1; output; 
   a=4; output; 
   a=5; output; 
   run; 
data want1;
   set have;
   b+a;
   run; 
proc print; 
   run; 
data want2;
   set have;
   retain b;
   if _n_=1 then b=a;
   else b=b+a;
   run; 
proc print; 
   run;

Re: how to sum a value in row 1 with row 2

data_null__ — Thu, 12 Mar 2015 14:05:10 GMT

No I am referring to sum statement the reply from xia that RW9 is saying doesn't work. RW9 doesn't know sum statement.

Re: how to sum a value in row 1 with row 2

Sangram — Thu, 12 Mar 2015 15:00:54 GMT

Try this

DATA data1;

INFILE datalines;

INPUT A ;

B + A;

datalines;

;run;

proc print data=data1;run;

Re: how to sum a value in row 1 with row 2

Reeza — Thu, 12 Mar 2015 17:08:14 GMT

If you have the license for SAS ETS this is called a running sum and it should be under:

Analyze -> Time Series -> Prepare Time Series Data

I think it should work even if you don't have time series data but don't have EG to actually test it with.

Re: how to sum a value in row 1 with row 2

t_kingsbury — Fri, 13 Mar 2015 09:19:47 GMT

If you were building in Enterprise Guide are you using data step (Program) or query builder?

In query builder you would write a series of 'Advanced Expressions' in the 'Computed Column' menu

For B1 it would be a simple t1.A1

For B2 it would be sum(t1.A1,t1.A2)

For B3 it would be sum(t1.A1,t1.A2,t1.A3)

and you would need to do one computed column at a time until you have all your columns (you can press the validate button in the advanced expression menu to test that the logic is correct)

in data step it would be

DATA test; (<Data set you are creating)

SET question; (<Data set you are calculating from)

b1=a1;

b2=a1+a2;

b3=a1+a2+a3;

RUN;

Re: how to sum a value in row 1 with row 2

TomKari — Fri, 13 Mar 2015 21:43:37 GMT

I don't think there's a reasonable way to do it using the EG facilities. I recommend you follow the advice in the other posts to write a short SAS program to do it.

Tom

Re: how to sum a value in row 1 with row 2

RogerSpeas — Wed, 18 Mar 2015 00:01:06 GMT

The query builder use SQL so in general, you cannot access the value of a previous rows as SQL does not in general terms respect a concept of data row order.

Now with said, might your data have some column that can be used as a unique identiifier, and might the summing be based on the order of that "unique" column... say somethig like date?

Given this assumption, you could use a self-join with an inequality and group summary.

Take for example the SASHELP.AIR table... date and total number of international miles traveled. Join the AIR table with itself, and modify the join operator in the TABLES window so that T1.DATE >= T2.DATE. In the SELECT DATA window, add t1.DATE, t1.AIR, and sum(T2.AIR). Edit the groups to include only T1.DATE.

PROC SQL;
CREATE TABLE WORK.QUERY_FOR_AIR AS

SELECT t1.DATE, t2.AIR,

/* SUM_of_AIR */ (SUM (t2.AIR)) AS SUM_of_AIR

FROM SASHELP.AIR t1

INNER JOIN SASHELP.AIR t2 ON (t1.DATE >= t2.DATE)

GROUP BY t1.DATE;

;