Hello SAS Users, I have a long data set with daily data across 6 weeks. I am trying calculate the weekly mean for each ID for weeks that have 3 or more days of data. My data looks like this: ID Week DayofWeek StudyDay SleepTime 1 1 1 1 360 1 1 2 2 450 1 1 3 3 320 1 1 4 4 420 1 1 5 5 220 1 1 6 6 400 1 1 7 7 360 1 2 1 8 320 1 2 2 9 . 1 2 3 10 220 1 2 4 11 . 1 2 5 12 400 1 2 6 13 . 1 2 7 14 450 1 3 1 15 . 1 3 2 16 . 1 3 3 17 . 1 3 4 18 . 1 3 5 19 . 1 3 6 20 420 1 3 7 21 220 What I want is this: ID Week DayofWeek StudyDay SleepTime WeeklyAverage 1 1 1 1 360 361.4285714 1 1 2 2 450 361.4285714 1 1 3 3 320 361.4285714 1 1 4 4 420 361.4285714 1 1 5 5 220 361.4285714 1 1 6 6 400 361.4285714 1 1 7 7 360 361.4285714 1 2 1 8 320 347.5 1 2 2 9 . 347.5 1 2 3 10 220 347.5 1 2 4 11 . 347.5 1 2 5 12 400 347.5 1 2 6 13 . 347.5 1 2 7 14 450 347.5 1 3 1 15 . . 1 3 2 16 . . 1 3 3 17 . . 1 3 4 18 . . 1 3 5 19 . . 1 3 6 20 420 . 1 3 7 21 220 . Here, there was no average calculated for Week 3 because there were not sufficient days of sleep data. I've tried this code but it is producing duplicate days: proc means data=work.have; class week ID ; var SleepTime; output out=work.summary mean= n= nmiss= / autoname; run; proc sql noprint; create table work.merge as select a.* , b.* from work.have a ,work.summary b where a.ID = b.ID order by a.ID, a.SleepTime; quit; proc sort data=work.merge; by Week; run; data work.merge; set merge; by week; if last.ID then do; output; if SleepTime_N >=3 then SleepTime=SleepTime_Mean; else SleepTime=.; ID="average of ID"; output; end; if index (ID, 'Avg')=0 then output; run; Your insight is greatly appreciated! Best, Stephanie
... View more