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
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.
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.
I think the query builder is strictly SQL, which does not honor the lag function.
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 25. 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.