topic Counting observations in consecutive year columns in SAS Programming

Counting observations in consecutive year columns

Zozo_star — Fri, 22 May 2020 05:00:18 GMT

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.

Re: Counting observations in consecutive year columns

ed_sas_member — Fri, 22 May 2020 08:53:39 GMT

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,

Re: Counting observations in consecutive year columns

ballardw — Fri, 22 May 2020 14:18:42 GMT

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?

Re: Counting observations in consecutive year columns

Zozo_star — Sun, 21 Jun 2020 15:21:08 GMT

Thanks, worked!

Re: Counting observations in consecutive year columns

Zozo_star — Sun, 21 Jun 2020 15:21:55 GMT

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