BookmarkSubscribeRSS Feed
div44
Calcite | Level 5

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

3 REPLIES 3
Reeza
Super User

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
ballardw
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 912 views
  • 0 likes
  • 4 in conversation