BookmarkSubscribeRSS Feed
CasperH_
Calcite | Level 5

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

13 REPLIES 13
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

have:

A    

1

4

5

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;

data_null__
Jade | Level 19

You don't need RETAIN with SUM statement.

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

art297
Opal | Level 21

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Ah, ok Smiley Happy

data_null__
Jade | Level 19

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

data_null__
Jade | Level 19

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;

3-12-2015 9-01-28 AM.png
Sangram
Calcite | Level 5

Try this

DATA data1;

INFILE datalines;

INPUT A ;

B + A;

datalines;

8

10

3

7

12

4

9

15

10

2

;run;

proc print data=data1;run;

Reeza
Super User

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.



t_kingsbury
Calcite | Level 5

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;

TomKari
Onyx | Level 15

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

RogerSpeas
SAS Employee

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;

;

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 13 replies
  • 5968 views
  • 0 likes
  • 10 in conversation