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.
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,
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,
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?
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.