BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
TheoIV
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
TomKari
Onyx | Level 15

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

View solution in original post

5 REPLIES 5
sasspan
Obsidian | Level 7

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
Super User

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
Onyx | Level 15

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
Calcite | Level 5

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

sasspan
Obsidian | Level 7

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!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

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
  • 5 replies
  • 3031 views
  • 1 like
  • 4 in conversation