DATA Step, Macro, Functions and more

scanning a row to find 2 consecutive values that are equal

Reply
Super Contributor
Posts: 279

scanning a row to find 2 consecutive values that are equal

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 .
Super User
Posts: 19,855

Re: scanning a row to find 2 consecutive values that are equal

Is number 1 i your example correct?

Super Contributor
Posts: 279

Re: scanning a row to find 2 consecutive values that are equal

Yes, it's correct. He was retained twice. I may have to ignore multiple retentions. There's    only a handful.

Super User
Posts: 19,855

Re: scanning a row to find 2 consecutive values that are equal

Arrays are one of the easiest ways to do this.

 

Look at the example titled 

Identify patterns across variables using arrays

 

 

 

 

Super Contributor
Posts: 279

Re: scanning a row to find 2 consecutive values that are equal

Thanks for the info. I need to think through my data some more and what I really want to accomplish.

Super User
Posts: 11,343

Re: scanning a row to find 2 consecutive values that are equal

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;
Super Contributor
Posts: 279

Re: scanning a row to find 2 consecutive values that are equal

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

Super Contributor
Posts: 279

Re: scanning a row to find 2 consecutive values that are equal

Thank you for the code. I need to think more about what I want as my end result.

Ask a Question
Discussion stats
  • 7 replies
  • 292 views
  • 2 likes
  • 3 in conversation