How to create an advanced expression formula with two specific time periods?

[andimo89]

Hello All! 

I am somewhat new to SAS EG and came across this problem. Currently, I am moving a project from excel into SAS and I am trying to figure out a solution. 

Essentially, I am trying to figure out a percent change from two specific periods. For example, the percent formula I am trying to re-create if (Fall 2019 DegreeCount) - (Fall 2018 DegreeCount) / (Fall 2018 DegreeCount). In excel it was done manually such as (G19-G20)/G20. How can I do that here in SAS enterprise guide? 

Example Data:

AcademicYr TermCode TermYear       School of Major         DegreeCount    CertCount

2019            201970     Fall 2019              School A                  500                        45

2019            201980     Spring 2020          School B                  120                        30

2019            201990     Summer 2020       School C                  5                           20

2018            201870      Fall 2018              School A                   2                            1

2018            201880      Spring 2019          School B                  6                           300

2018            201890      Summer 2019       School C                 2                          200

[mkeintz]

Since SAS keeps only one "row" of data in memory at a time, you can't exactly replicate access to row 19 when calculating a value in row 20.

But you can use the "lag" function, as in:

data want;
  set have;
  if termyear=:'Fall' then pct_change=divide(dif(degreecount),lag(degreecount));
run;

  Whenever termyear starts with 'Fall' the lag function updates a fifo queue, by extracting the last time it was invoked (i.e. the previous "Fall") and storing the current value into the queue.  In this case the lag function is a queue of size 1   ("lag2" would have a size of 2, etc.).

The dif function in nothing more than current degreecount minus the lagged value of degreecount (i.e. the change in degreecount.

Now if I had done this:

data want;
  set have;
  if termyear=:'Fall' then pct_change=dif(degreecount)/lag(degreecount);
run;

I would get the same numeric results, but also an error message because the first instance of "Fall" would retrieve a missing lagged value, which would cause this log note:

NOTE: Missing values were generated as a result of performing an operation on missing values.
Each place is given by: (Number of times) at (Line):(Column).
1 at xxx:42 1 at xxx:59

Using the DIVIDE function instead avoided the note.

[andimo89]

I appreciate your reply. I apologize for not having the proper nomenclature for this but could this be done inside they query builder. I am incredible novice at SAS code and was wondering if this can be done from the advanced expression button inside of a query builder. 

[mkeintz]

I think the query builder is strictly SQL, which does not honor the lag function.