Novice Question: How to Create a Computed Column Based on Row Values

[TheoIV]

I am starting to learn SAS EG and using it for my dissertation.  I am not sure if I have to run code or if I can do the following in the computed column interface:

THE PROBLEM:

I have a table (STATE) that (to simplify) consists of the following columns:

StateYRKey, State, Year, ChgYr1, ChgYear5, ChgYear10, GSP  (e.g.,  AK1960, AK, 1960, 1961, 1965, 1970, 123456)

I want to create a computed column for various changes in GSP (again simplifying) Yr1GSPChg, Yr5GSPChg, Yr10GSPChg

I would like to code something like (or simplier):

For i in {AK..WY} Do

  For j = {1960..2012} Do

  BEGIN

* The key is a concatenation.  I want to access values for specific rows to compute the appropriate difference)  

  STATE.(Concatenate i,j).Yr1GSPChg = STATE.(Concatenate i,j)GSP - STATE.(Concatenate i,j -1).GSP

    STATE.(Concatenate i,j).Yr5GSPChg = STATE.(Concatenate i,j)GSP - STATE.(Concatenate i,j -5).GSP

    STATE.(Concatenate i,j).Yr10GSPChg = STATE.(Concatenate i,j)GSP - STATE.(Concatenate i,j -10).GSP

  END

In other words,  since the key is a concatenation of year and state, I want new columns with with the GSP difference for various years for each state.  I then intend to do some further analysis.  I have found very little information on how to reference a specific row or do this type of thing in EG Filter/Query.

There has to be an easy way, and I may have just looked at this too long.  Any help is appreciated.

[sasspan]

There's a simple function to get a row number added as a column, then you can reference that computed column from there.

Go to computed columns, advanced expression, type MONOTONIC()  just like that.  Label and Name your new row number column whatever you want, and you will have a column that lists the row number ascending

[Reeza]

You need to look at lag functions and possibly re-merging the data.

Data in SAS is processed line by line, not pulled in and reference like a matrix (as you seem to be referring to it).

There are lots of ways around this though, too many to get into here.

[TomKari · Accepted solution]

If I understand your objective correctly, here's what I consider the clearest way to do it.

First, I think it'll be easiest if you restructure your data into a table that has three columns: State, Year, and GSP. So it would look like

this:

State Year GSP
===== ==== ===
AK 1960 123456
... 
FL 1972 234567
... 
WY 2012 345678

Assuming 50 states and 53 years, this table will have 2650 records.

Now in Enterprise Guide, the trick is to create a table with your current GSP and the one from the year before.

To do this (assuming your table is named StateData, to ensure we don't confuse the table and the column):

1. Start a new QueryBuilder dialog on StateData.

2. Click Add Tables, and add StateData a second time. It will be referred to as t2, and your original StateData will be t1. They are actually both the same table. This is a little unusual, but completely legitimate.

3. Click Join Tables. In this dialogue:
3a. Make sure t1 is joined to t2 on State with an inner join.
3b. Right click the Join symbol, and click Properties.
3c. In the Properties window, click Edit... to create a filter for the join.
3d. In the filter, use the icons or type in the filter value to be t2.year = t1.year - 1.
3e. Close the Tables and Joins dialoge.

4. On your main query dialogue, select all of the values of t1, and select t2.GSP, renaming it t2.Yr1GSP.

5. Create a new advanced expression, call it Yr1GSPChg, and set it equal to t1.GSP - t2.GSP.

When you run this, it should give you your one year time series. If I were you, I'd add the State and Year from t2 to the results temporarily, and see if everything looks good.

This approach works the same for the 5 and 10 year differences.

One of the things you'll need to do it figure out what to do about the years that don't have a -1, -5, or -10 year value. There are a number of different approaches you can use.

Good luck!

  Tom

[TheoIV]

Thanks Tom.... exactly what I want to do!... very helpful.  Why didn't I think of that?

[sasspan]

Tom this helped me too!  I am using dates of service and want to know how long it has been since the claims last fill.  I am just going to rank the columns and join on claim_id and the rank=rank-1.  THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!