Recode daily date data

[srobinson5]

Dear SAS users,

I am trying to analyze daily data from a longitudinal study. I would like to code the calendar dates into study week and days (i.e., # of weeks and days they've been enrolled in the study since the first visit). All participants have varying start dates and varying number of days they were in the study. Currently the data I have look like this:

StudyID	Date	Steps	Goal
1	4/20/2020	3001	3000
1	4/21/2020	2987	3000
1	4/22/2020	4313	3500
1	4/23/2020	4380	3500
2	3/13/2020	6098	6200
2	3/14/2020	7022	6200
2	3/15/2020	5980	6400
2	3/16/2020	5750	6400

I would like for it to look like this:

StudyID	StudyWeek	StudyDay	Steps	Goal
1	1	1	3001	3000
1	1	2	2987	3500
1	1	3	4313	3500
1	1	4	4380	3000
2	1	1	6098	6200
2	1	2	7022	6200
2	1	3	5980	6400
2	1	4	5750	6400

Typically, I would post code I have that I need help with, but I don't know where to even start to do this. Any help is very much appreciated!

Best,

Stephanie

[Reeza]

Can you expand your example to show more than one week? How is a week defined? Is it from Sunday to Monday so based on Calendar dates or is it based on something else?

[ballardw]

And why is week so important? You can run into a number issues depending on what you mean by "week". Such as if the first date is a Wednesday, does the week end on Saturday, Sunday or Tuesday to increment "week"?

How do you assign a value of "day" if there is a gap in the dates?

[srobinson5]

Thank you for your help in this problem. I would like to code for week to denote which week in the trial the participant is in. Because the goals incrementally increase week by week, I'd like to have this variable so I can assess for variation across weeks in the study. I am not concerned with calendar weeks, but care that week 1= days 1 through 7, week 2 = days 8 through 14, etc. There might be days when no data was present, so the date is missing. I'd like the "want" dataset to code the week and day for those missing dates, so it reflects continuous participation and missing data. I hope this is more clear and very much appreciate your expertise in advance.

Thanks,

Stephanie

[novinosrin]

data have;
input StudyID 	Date :mmddyy10.	Steps	Goal;
format date mmddyy10.;
cards;
1	4/20/2020	3001	3000
1	4/21/2020	2987	3000
1	4/22/2020	4313	3500
1	4/23/2020	4380	3500
2	3/13/2020	6098	6200
2	3/14/2020	7022	6200
2	3/15/2020	5980	6400
2	3/16/2020	5750	6400
;
data want;
 if 0 then set have(keep=studyid);
 call missing(StudyWeek);
 do StudyDay=1 by 1 until(last.studyid);
  set have;
  by studyid;
  if mod(StudyDay,7)=1 then StudyWeek=sum(StudyWeek,1);
  output;
 end;
run;

StudyID	StudyWeek	StudyDay	Date	Steps	Goal
1	1	1	04/20/2020	3001	3000
1	1	2	04/21/2020	2987	3000
1	1	3	04/22/2020	4313	3500
1	1	4	04/23/2020	4380	3500
2	1	1	03/13/2020	6098	6200
2	1	2	03/14/2020	7022	6200
2	1	3	03/15/2020	5980	6400
2	1	4	03/16/2020	5750	6400