My data set consists of grade progressions of students over 13 school years (04-05 to 16-17). Every student identified has been retained at least once during his/her enrollment in our system. My task is to identify which school year the retention occured. The header row represents the school year, e.g. grade17 is the 16-17 school year, grade16 is the 1516 school year, etc. The values represent the grade the student was in. A zero stands for kindergarten; a missing value means the student was not enrolled in that particular year.
Student 1 should be flagged as being retained in grade 12 after the 14-15 school year and the 15-16 school year.
Student 2 should be flagged as being retained in grade 10 after 13-14 school year.
Student 3 should be flagged as being retained in grade 0 (kindergarten) after the 05-06 school year.
ID | grade17 | grade16 | grade15 | grade14 | grade13 | grade12 | grade11 | grade10 | grade09 | grade08 | grade07 | grade06 | grade05 |
1 | 12 | 12 | 12 | 11 | 10 | 9 | 9 | 7 | 6 | 5 | 4 | 3 | 2 |
2 | 12 | 11 | 10 | 10 | 9 | . | . | . | . | . | . | . | . |
3 | 10 | 9 | 8 | 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 | 0 | . |
Is number 1 i your example correct?
Yes, it's correct. He was retained twice. I may have to ignore multiple retentions. There's only a handful.
Arrays are one of the easiest ways to do this.
Look at the example titled
Thanks for the info. I need to think through my data some more and what I really want to accomplish.
What actual VALUE do you want in your flag variable? it is not clear from your question what your output data set would look like.
Also what is the actual rule when there are multiple choices such as with ID=1?
This will set a flag for the "latest" grade for a student or none if there are no sequences.
data want; set have; array g {5:17} grade05-grade17; do i = 17 to 6 by -1; if not missing(g[i]) and g[i-1] = g[i] then do; Flag=g[i]; leave; end; end; run;
I actually need 2 flags - one to show the grade in which the student was retained and the other to show when he was retained. For student 2, it would grade_retained = 10 and when_retained = grade14
Thank you for the code. I need to think more about what I want as my end result.
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.