03-12-2015 05:53 AM
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.
03-12-2015 09:53 AM
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:
if _n_=1 then b=a;
03-12-2015 10:00 AM
RW9: I think DN was referring to the following form:
Correct but still more complex than needed:
if _n_=1 then b=a;
Better (per data_null_'s subsequent post):
03-12-2015 10:02 AM
RTM Not SUM function SUM STATEMENT.
03-12-2015 01:08 PM
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.
03-13-2015 05:19 AM
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)
03-13-2015 05:43 PM
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.
03-17-2015 08:01 PM
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.
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;