topic Re: How to create an advanced expression formula with two specific time periods? in SAS Enterprise Guide

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

andimo89 — Thu, 08 Apr 2021 23:46:17 GMT

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

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

mkeintz — Fri, 09 Apr 2021 00:45:36 GMT

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.

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

andimo89 — Fri, 09 Apr 2021 01:27:53 GMT

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.

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

mkeintz — Fri, 09 Apr 2021 02:53:29 GMT

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