Counting observations in consecutive year columns

[Zozo_star]

If I have a wide data set where each row represents a student and the columns show the years within which they got punished eg:

Student Year1 Year2 Year3 Year4

A             1         1         -         1

B             -         -          1         1

C             1        1          1         1

So I need a code that can show me the number of students who have been punished across 4,3,2 consecutive years.

[ed_sas_member · Accepted solution]

Hi @Zozo_star 

Please try this. Does the following output meet your needs?

data have;
	infile datalines dsd missover dlm=" ";
	input Student $ Year1 Year2 Year3 Year4;
	datalines;
A 1 1  1
B   1 1
C 1 1 1 1
;
run;

proc transpose data=have out=have_tr(rename=(col1=Punished)) name=Year ;
	var year:;
	by student;
run;

data have_consec;
	do i=1 until (last.student);
		set have_tr;
		by student;
		count+1;
		if missing(Punished) then count=0;
		else if first.student then count=1;
	end;
run;

proc means data=have_consec max noprint;
	var count;
	class student;
	ways 1;
	output out=want (drop=_:) max=max_Yr_consec;
run;

proc freq data=want;
	title "Number of students who have been punished across 4,3,2 consecutive years";
	table max_Yr_consec / nocum nopercent;
	label max_Yr_consec="Consecutive years";
run;

Output:

Best, 

[Zozo_star]

Thanks, worked!

[ballardw]

Monkey wrench question:

Suppose you have 6 years of data and one student has 2 consecutive years and 3 consecutive years with a year without in between. Which gets counted?

[Zozo_star]

Once I knew the frequencies as suggested above, I then set a condition to pick all with any 2, 3 etc.