Hello Community, I am working on a data analysis problem for which I would greatly appreciate some programming help. Basically, I need to know whether or not subjects in my dataset reported drug use (Drug A) during a research study at specific time points. Specifically, I would like to know whether subjects reported use of Drug A during each of the following time periods during the study: Prior to the study Screening date (dates<Screen_Date) Between the Screening date and Visit 2 (Screen_Date<=dates<Visit 2) Between Visit 2 and Visit 3 (Visit 2<=dates<Visit 3) Please see an example of the desired output data set (Want) below. The VISITNO corresponds to the number of each of the 3 study visits where Visit 1 would correspond to the Screening date. Want: PID VISITNO Date Drug A Use 01 1 03/30/2018 1 01 2 05/02/2018 0 01 3 05/30/2018 0 02 1 04/09/2018 1 02 2 05/09/2018 1 02 3 . . ….. I have also provided an example of the two data sets I have to produce this output. The first is a dataset listing the visit numbers and visit dates for each subject (Have 1). Note that the Screening Visit is a separate variable; however, I would like to have a single variable in my output dataset that designates the Screening Visit as Visit 1 (See Want above). Please note that some visits may not have attended a study visit, hence, the missing data for a visit date (subject 02 in the example below). Have 1: Subject ID Screen_Date VisitNo Visit_date 01 03/30/2018 2 05/02/2018 01 03/30/2018 3 05/30/2018 02 04/09/2018 2 05/09/2018 02 04/09/2018 3 . The second dataset that I have indicates the dates that drug use occurred on (Have 2). That is, Drug A use is indicated by a "1" in the "D1....D7 drug A use" columns. Each row of data for each participant represents an entire week. The "D1 drug use" column indicates drug use for the "D1 date" column, the "D2 drug use" column indicates drug use for the "D2 date" column, and so on. Please also note that some participants may not have completed a study visit. Therefore, the drug use for that visit would be missing (e.g., Subject ID 02 in the example below). Have 2: Subject ID D1 date D2 date D3 date D4 date D5 date D6 date D7 date D1 drug A use D2 drug A use D3 drug A use D4 drug A use D5 drug A use D6 drug A use D7 drug A use 01 . . . 02/28/18 03/01/18 03/02/18 03/03/18 . . . 0 0 1 0 01 03/04/18 03/05/18 03/06/18 03/07/18 03/08/18 03/09/18 03/10/18 0 0 0 0 0 1 0 01 03/11/18 03/12/18 03/13/18 03/14/18 03/15/18 03/16/18 03/17/18 0 1 0 0 0 0 0 01 03/18/18 03/19/18 03/20/18 03/21/18 03/22/18 03/23/18 03/24/18 0 0 0 0 0 0 0 01 03/25/18 03/26/18 03/27/18 03/28/18 03/29/18 03/30/18 03/31/18 0 0 1 0 0 0 0 01 04/01/18 04/02/18 04/03/18 04/04/18 04/05/18 04/06/18 04/07/18 0 0 0 0 0 0 0 01 04/08/18 04/09/18 04/10/18 04/11/18 04/12/18 04/13/18 04/14/18 0 0 0 0 0 0 0 01 04/15/18 04/16/18 04/17/18 04/18/18 04/19/18 04/20/18 04/21/18 0 0 0 0 0 0 0 01 04/22/18 04/23/18 04/24/18 04/25/18 04/26/18 04/27/18 04/28/18 0 0 0 0 0 0 0 01 04/29/18 04/30/18 05/01/18 05/02/18 05/03/18 05/04/18 05/05/18 0 0 0 0 0 0 0 01 05/06/18 05/07/18 05/08/18 05/09/18 05/10/18 05/11/18 05/12/18 0 0 0 0 0 0 0 01 05/13/18 05/14/18 05/15/18 05/16/18 05/17/18 05/18/18 05/19/18 0 0 0 0 0 0 0 01 05/20/18 05/21/18 05/22/18 05/23/18 05/24/18 05/25/18 05/26/18 0 0 0 0 0 0 0 01 05/27/18 05/28/18 05/29/18 . . . . 0 0 0 . . . . 02 . . . . . . 03/10/18 . . . . . . 0 02 03/11/18 03/12/18 03/13/18 03/14/18 03/15/18 03/16/18 03/17/18 1 1 1 0 1 1 0 02 03/18/18 03/19/18 03/20/18 03/21/18 03/22/18 03/23/18 03/24/18 1 1 1 0 1 1 0 02 03/25/18 03/26/18 03/27/18 03/28/18 03/29/18 03/30/18 03/31/18 1 1 1 0 1 1 0 02 04/01/18 04/02/18 04/03/18 04/04/18 04/05/18 04/06/18 04/07/18 1 1 1 0 1 1 0 02 04/08/18 04/09/18 04/10/18 04/11/18 04/12/18 04/13/18 04/14/18 1 1 1 0 1 1 0 02 04/15/18 04/16/18 04/17/18 04/18/18 04/19/18 04/20/18 04/21/18 1 1 1 0 1 1 0 02 04/22/18 04/23/18 04/24/18 04/25/18 04/26/18 04/27/18 04/28/18 1 1 1 0 1 1 0 02 04/29/18 04/30/18 05/01/18 05/02/18 05/03/18 05/04/18 05/05/18 1 1 1 0 1 1 0 02 05/06/18 05/07/18 05/08/18 05/09/18 . . . 1 1 1 1 . . . ….. Note: The data includes 30 days prior to each subject’s Screening Date; hence, the missing data for some cells.
... View more