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
B+A;
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;
You don't need RETAIN with SUM statement.
The variable on the left is initialized to 0 and retained.
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;
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;
Ah, ok
No I am referring to sum statement the reply from xia that RW9 is saying doesn't work. RW9 doesn't know sum statement.
RTM Not SUM function SUM STATEMENT.
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;
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.
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;
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
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;
;
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.
Ready to level-up your skills? Choose your own adventure.