topic Re: Duplicate rows between a time period in transversal data in SAS Programming

Duplicate rows between a time period in transversal data

Ely — Tue, 26 Nov 2019 13:29:51 GMT

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.

Re: Duplicate rows between a time period in transversal data

andreas_lds — Tue, 26 Nov 2019 13:38:34 GMT

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-data-AKA-generate/ta-p/258712 if you don't know how to do this.

Re: Duplicate rows between a time period in transversal data

mkeintz — Tue, 26 Nov 2019 16:34:24 GMT

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.

Re: Duplicate rows between a time period in transversal data

Ely — Wed, 27 Nov 2019 08:18:54 GMT

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.

Re: Duplicate rows between a time period in transversal data

Ksharp — Wed, 27 Nov 2019 12:43:30 GMT

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;

Re: Duplicate rows between a time period in transversal data

Ely — Tue, 03 Dec 2019 13:49:49 GMT

Many thanks for your help