Please find a sample dataset It is a dataset of students and their attendance, which is cumulative over 12 days. I need to find the earliest day when their attendance crossed 5 days over a period of 7 days. Thank you
ID Day1 Day2 Day3 Day4 Day5 Day6 Day7 Day8 Day9 Day10 Day11 Day12
Robin 1 2 3 4 5 6 7 8 9 10 11 12
Michael 1 2 2 3 4 5 5 6 7 8 9 10
Jeremy 1 2 3 3 4 5 6 6 7 7 7 7
Matt 1 1 1 2 2 3 3 3 4 4 5 6
Sara 1 2 3 4 5 5 5 6 6 7 8 9
Tony 1 2 2 2 2 3 3 3 4 4 4 4
Expected output
Name Start_day Day_crossing_value_5
Robin 1 5
Michael 1 6
Jeremy 1 6
Matt 5 12
Sara 1 5
Tony . .
It does not need to be the first day, if using the first day at start_date does not give the answer, then I want the program to move to the next one. In this way the start_day for Matt reached 5, because on day12 his value was 5 greater than the value(2) on day5. So over a period of 7days he crossed 5 attendace units. If you would have used day1 as start date for Matt, he would have reached only 3 by day 7.
I am using
array day(*) day1-day12;
do i = 1 to 12 until (day(i+6) - day(i) > 4) ;
end;
run;
Thanks
Matt never reaches 5 days in a 7 day period
Matt 1 1 1 2 2 3 3 3 4 4 5 6
Attendence 1 0 0 1 0 1 0 0 1 0 1 1
Pleas post test data in a readable format. Your logic needs a bit of clarification as following it (from my understanding), yields a different result dataset:
data have; input id $ day1 day2 day3 day4 day5 day6 day7 day8 day9 day10 day11 day12; datalines; Robin 1 2 3 4 5 6 7 8 9 10 11 12 Michael 1 2 2 3 4 5 5 6 7 8 9 10 Jeremy 1 2 3 3 4 5 6 6 7 7 7 7 Matt 1 1 1 2 2 3 3 3 4 4 5 6 Sara 1 2 3 4 5 5 5 6 6 7 8 9 Tony 1 2 2 2 2 3 3 3 4 4 4 4 ; run; data want (keep=id start_day day_crossing_value_5); set have; array day day:; start_day=1; do i=1 to dim(day); if day{i} > 5 and missing(day_crossing_value_5) then day_crossing_value_5=i-1; end; run;
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... has instructions on how to turn an existing SAS data set into data step code that may be posted here to recreate your data for us to use testing or just understand the actual structure of your data.
It also avoids us making choices when writing a data step to recreate a data set from your posted example data that may be incompatible with your data types.
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.