# Reformat data problem for Cox model in SAS

I am relatively new to SAS. Currently I am taking survival analysis course, and I am really stuck on reformatting a data into 'counting process' (start, stop) form for fitting a Cox regression model with time-varying covariate (https://support.sas.com/resources/papers/proceedings12/168-2012.pdf). To keep the question short, below please find the much simplified version of the data I have now, and the format I want it to be:

data have;
input id site $a1-a5 outcome$@@;
datalines;
1 a 1 1 0 1 1 y
1 b 0 0 1 0 0 n
2 b 1 1 0 0 0 n
2 c 0 0 1 0 1 n
2 d 0 0 0 1 0 n
3 a 1 0 0 1 0 y
;
run;

id: unique identification for each person

site: working site

a1-a5: attendance for week 1-5 (1=person works at the site in that week, 0 =otherwise)

outcome: binary outcome of interest (y/n, if the person has y, it means the event happens in his/her last attendance week)

There are actual dates for week 1-5, for example:

week 1: 2022/07/31 - 2022/08/06

week 2: 2022/08/07 - 2022/08/13

...

week 5: 2022/08/28 - 2022/09/03

Note that not everyone works every week, and some people work at different sites. I am not too sure if it is necessary but I would like to introduce a week indicator variable named 'week', as well as the start and stop variables so that the data format becomes:

data want;
input id site start $stop$ week outcome @@;
datalines;
1 a 2022/07/31 2022/08/06 1 n
1 a 2022/08/07 2022/08/13 2 n
1 b 2022/08/14 2022/08/20 3 n
1 a 2022/08/21 2022/08/27 4 n
1 a 2022/08/28 2022/09/03 5 y
2 b 2022/07/31 2022/08/06 1 n
2 b 2022/08/07 2022/08/13 2 n
2 c 2022/08/14 2022/08/20 3 n
2 d 2022/08/21 2022/08/27 4 n
2 c 2022/08/28 2022/09/03 5 n
3 a 2022/07/31 2022/08/06 1 n
3 a 2022/08/21 2022/08/27 4 y
;
run;

My concern is that since some people stay in the same working site throughout the entire time, some switch sites, some switch sites multiple times, some switch back and forth, some don't show up in some weeks, I just could not find the proper logic that could apply to the data (over 1000 people). Might someone be willing to provide guidance?

Thank you

My concern is that since some people stay in the same working site throughout the entire time, some switch sites, some switch sites multiple times, some switch back and forth, some don't show up in some weeks, I just could not find the proper logic that could apply to the data.

That's the beauty of the "counting process" data format. It allows you to use all of the information that you have about time periods and covariate values for individuals, while recognizing the limits to information available outside the specified time periods.

You need to remember that a Cox model proceeds with its analysis event time by event time, only evaluating the covariate values that are in place at each event time for the individuals at risk. Covariate values for an individual before or after the event time being evaluated don't matter for that event time. That's what I had to struggle with, at least, when I learned about this.

Thus in Cox models the start time is treated as left-truncated. That is, the data row provides no information about the hazards for events prior to that time, as it provides no covariate values for event times prior to its start time.

Between start and stop, the individual is considered "at risk" for events that happen during that time period. The data row thus provides information for evaluating events in other individuals that occur during that period.

The stop time is an event time or a right-censoring time, corresponding to the covariate value. That data row provides no information for evaluating later event times.

With id values for individuals you can also account for individual-specific associations with outcome, if multiple events are possible for an individual.

So if someone switches back and forth among work sites, that individual provides information about event associations with a particular work site during the time period(s) that she is working at that site. If there are time periods completely missing for an individual, then that individual simply doesn't provide information during that time period. With the reliance of Cox models on instantaneous values of covariates at event times, that works out OK.

• Thank you for your explanation. Might you be willing to also guide me through the reformatting from have to want? Aug 5, 2022 at 19:17
• @RonaldCarlos coding-specific issues are off-topic on this site, and I don't use SAS at all. Do a web search for converting a "wide" data format to "long." I use the pivot_longer() function in the R tidyr package, but still usually go through trial-and-error on data subsets first. If you really have many fewer time periods than your 1000 individuals, you might be better off with a discrete-time survival model. You would use the week numbers and long format, but wouldn't need the actual dates.
– EdM
Aug 5, 2022 at 19:59
• @RonaldCarlos stats.oarc.ucla.edu/sas/modules/… might help.
– EdM
Aug 5, 2022 at 20:06
• Thank you @EdM once again for the helpful comment. Aug 5, 2022 at 21:00
• PROC TRANSPOSE should help to go from have to want. Aug 6, 2022 at 8:44