I have two datasets, both including ID and day. I would like to merge them by ID and day but include the day variables from both files in the data I want, i.e. include them also when there is no match on the day variable.   data have; input ID Day; datalines; 1 1 1 2 1 3 ;   Data have2; Input ID Day; 1 . 1 2 1 3 ;   Data want; ID Day Day 1 1  . 1 2 2 1 3 3 ... View more

I have a data file describing users (ID), time periode a service is in use (FromYrMon-ToYrMon) and hours of services (hours). There are two problems. First, the time periods partly overlap. There are also some gaps between the time periods but that is probably correct. Second, the hours of services are not consistently coded in the sense that the same time period (or parts of it) may have differences in hours. The file I want, includes for each ID,  one observation per month, the maximum of hours for that month and 0 for months where there are gaps between time periods. I guess the solution will be something like 1) transposing, 2) fill in gaps and 3) pick the highest number of hours for each month.   data have; input ID FromYrMon YYMMN6 ToYrMon YYMMN6. Hours comma4.2; datalines; 1 201701 201711 0.75 1 201704 201711 1.20 1 201801 201802 4.00 2 201710 201802 2.00 ; Data want; ID YYMon Hours 1 201701 0.75 1 201702 0.75 1 201703 0.75 1 201704 1.20 1 201705 1.20 1 201706 1.20 1 201707 1.20 1 201708 1.20 1 201709 1.20 1 201710 1.20 1 201711 1.20 1 201712 0.00 (NB) 1 201801 4.00 1 201802 4.00 2 201701 0.00 2 201702 0.00 2 201703 0.00 2 201704 0.00 2 201705 0.00 2 201706 0.00 2 201707 0.00 2 201708 0.00 2 201709 0.00 2 201710 2.00 2 201711 2.00 2 201712 2.00 2 201801 2.00 2 201802 2.00   ... View more

Data Have illustrate a case where a patients receive a specific service in specific periods before death - which is at day 0. I would like to transpose this file and make it like Data Want where there is one row per day per ID and a dummy variable taking the value of 1 if the service is received, 0 if not. In total, the period covered in this example is 14 days (incl. day 0).   Data Have: Obs ID Startday Endday 1 1 -2 0 2 1 -8 -6 3 1 -11 -10 4 2 -5 -3 5 2 -8 -7   Data Want Obs ID Day Dummy 1 1 0 1 2 1 -1 1 3 1 -2 1 4 1 -3 0 5 1 -4 0 6 1 -5 0 7 1 -6 1 8 1 -7 1 9 1 -8 1 10 1 -9 0 11 1 -10 1 12 1 -11 1 13 1 -12 0 14 1 -13 0 15 2 0 0 16 2 -1 0 17 2 -2 0 18 2 -3 1 19 2 -4 1 20 2 -5 1 21 2 -6 1 22 2 -7 0 23 2 -8 1 24 2 -9 0 25 2 -10 0 26 2 -11 0 27 2 -12 0 28 2 -13 0   ... View more

I would like to combine two or more consecutive time periods into one, based on the condition that they have the same ID and the same value for another variable (hours). A new record in the output data will be generated if there is a new ID or a change in the value of hours within an ID. Additionally, if there is a break or a gap between the Enddate of one period and the Startdate of the next period within an ID, a new record will also be created in the output data.   Data Have: Rec ID Startdate Enddate Hours 1 1 01/01/2023 01/08/2023 8 2 1 01/09/2023 01/31/2023 8 3 1 02/01/2023 02/28/2023 4 4 1 03/01/2023 05/31/2023 8 5 1 06/01/2023 09/26/2023 8 6 2 01/01/2023 01/07/2023 8 7 2 01/10/2023 01/31/2023 8 8 2 02/01/2023 02/28/2023 4   Data Want: ID Startdate Enddate Hours 1 01/01/2023 31/01/2023 8 1 02/01/2023 02/28/2023 4 1 03/01/2023 09/26/2023 8 2 01/01/2023 01/07/2023 8 2 01/10/2023 01/31/2023 8 2 02/01/2023 02/28/2023 4   Based on a former discussion in SAS Communities I’m working on this code which doesn’t fully make it (for example, it doesn’t collapse record 4 and 5):   proc sort data=have out=have2; by id hours startdate; run; data want; set have2; by id hours; lag_enddate=lag(enddate); if not first.hours then do; if startdate-lag_enddate<=2 then delete_flag=1; end; if delete_flag then delete; drop lag_enddate delete_flag; run;       ... View more

I want to combine two or more time periods into one, contingent upon a) same ID, b) same value on another variable (hours) and gaps between time periode. If there is a gap between two periods of more than two days, then this should still be two periods (cf. ID2, first and second row):   Data have: ID                           Startdate               Enddate                Hours 1                             20230101             20230108             8 1                             20230109             20230131             8 1                             20230201             20230228             4 2                             20230101             20230108             8 2                             20230112             20230131             8 2                             20230201             20230228             4   Data want: ID                           Startdate               Enddate                Hours 1                             20230101             20230131             8 1                             20230201             20230228             4 2                             20230101             20230108             8 2                             20230112             20230131             8 2                             20230201             20230228             4 ... View more