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.
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
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
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.
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
Thanks Tom.... exactly what I want to do!... very helpful. Why didn't I think of that?
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!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: