Duplicate rows between a time period in transversal data

[Ely]

Hi everyone,

I am having a trouble when trying to derive monthly evolution of patients per line of treatment. I would like, for a period between 2016/01/01 and 2019/07/31, to split each patient information per month in order to be able to only keep the month on target. 

Dataset
Pat   Poduct   Line   Line_first_date   Line_end_date
1   ProdA   1   16/12/2010   23/02/2016
1   ProdB   2   10/03/2016   30/06/2017
1   ProdC   3   18/09/2017   20/01/2019
1   ProdD   4   05/03/2019   11/12/2019

Want
Pat   Month   Month_start_date   Month_end_date   Product   Line
1   1   01/01/2016   31/01/2016   ProdA   1
1   2   01/02/2016   29/02/2016   ProdA   1
1   3   01/03/2016   31/03/2016   ProdB   2
1   4   01/04/2016   30/04/2016   ProdB   2
1   5   01/05/2016   31/05/2016   ProdB   2
1   .    . . . .
1   .    . . . .
1   .    . . . .
1   41   01/05/2019   31/05/2019   ProdD   4
1   42   01/06/2019   30/06/2019   ProdD   4
1   43   01/07/2019   31/07/2019   ProdD   4

Do anyone have an idea of how to process?

Thank for your help.

[andreas_lds]

Please post the data in usable form, see https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... if you don't know how to do this.

[mkeintz]

In the data your provides (and I concur with @andreas_lds  request for a data step providing your sample input data), you have.

Pat   Poduct   Line   Line_first_date   Line_end_date
1   ProdA   1   16/12/2010   23/02/2016
1   ProdB   2   10/03/2016   30/06/2017
1   ProdC   3   18/09/2017   20/01/2019
1   ProdD   4   05/03/2019   11/12/2019

Line 2 ends on 30jun2017, but line 3 begins on 18sep2017.   What PRODUCT values do you want assigned to July and August 2017?  Please give a more complete description of what you want.  And the same question applies to the gap between lines 3 and 4, since the stopping point is July 2019.

Please provide a more comprehensive description of your rules for the output data.  Help us help you.

[Ely]

Hello,
Thank you for your help.
In July and August, I want to apply the previous product (ProdB).
The rules are:
- to apply the previous product when there is gap. The patient is at any time under treatment
- to apply the dominant product when there is an overlap of 2 products in the same month: If prod A is taken 12 days and Prod B 19 days, then B is dominant
Regarding @andreas_lds request, I am still trying what he suggested to me.

[Ksharp · Accepted solution]

Assuming there is not overlap date range for product.

data have;
input Pat   Product $  Line   (Line_first_date   Line_end_date) (: ddmmyy10.);
format Line_first_date   Line_end_date ddmmyy10.;
cards;
1   ProdA   1   16/12/2010   23/02/2016
1   ProdB   2   10/03/2016   30/06/2017
1   ProdC   3   18/09/2017   20/01/2019
1   ProdD   4   05/03/2019   11/12/2019
;

data temp;
 set have;
 do date=Line_first_date  to Line_end_date;
  output;
 end;
 drop Line_first_date   Line_end_date;
run;

data date;
 do date='01jan2016'd to '31jul2019'd;
  year=year(date);month=month(date);output;
 end;
 format date ddmmyy10.;
run;

data temp2;
 merge date(in=ina) temp;
 by date;
 if ina;
run;

proc sql;
create table want as
select year,month,max(pat) as pat,
min(date) as start format=ddmmyy10.,
max(date) as end format=ddmmyy10.,
max(product) as product,max(line) as line
 from temp2
  group by year,month;
quit;

[Ely]

Many thanks for your help