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.
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;
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.
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.