monthly age as per birth and death dates

[saraphdnz]

Hello,

I want to edit my code as per the birth and death dates but am unable to fix it for death dates.

I want monthly age as per the Date column which contains monthly dates.

If the death date is NOT BLANK I want the age calculation to stop at that date.

If the death date IS BLANK, the monthly age is calculated till the available date in the Date column.

Birth, Death and Date columns are all in  MDY format. only Death dates have missing values.

attached a sample file as well. any help would be appreciated.

DATA  AGE_MONTHS1;
SET TEST;
IF DEATH_DATE = . THEN
TODAY = DATE; /**MONTHLY DATES**/
DAYS = TODAY - BIRTH_DATE;
AGE=FLOOR((INTCK('month',BIRTH_DATE,TODAY) - (DAY(TODAY) < DAY(BIRTH_DATE))));
IF DEATH_DATE NE . THEN
/**THIS NEED TO FIX**/
TODAY = DEATH_DATE ; 
DAYS = TODAY - BIRTH_DATE;
AGE=FLOOR((INTCK('month',BIRTH_DATE,TODAY) - (DAY(TODAY) < DAY(BIRTH_DATE))));
RUN;

Regards,

Sara 

[PeterClemmensen]

Can you post your desired result given your sample data?

(Edited as a data step below)

data have;
input ID (Birth_Date Death Date Date)(:ddmmyy10.);
format Birth_Date Death Date Date ddmmyy10.;
infile datalines dlm = ',' dsd;
datalines;
1,30-08-1996,20-09-2019,31-01-2019
1,30-08-1996,20-09-2019,28-02-2019
1,30-08-1996,20-09-2019,29-03-2019
1,30-08-1996,20-09-2019,30-04-2019
1,30-08-1996,20-09-2019,31-05-2019
1,30-08-1996,20-09-2019,28-06-2019
1,30-08-1996,20-09-2019,31-07-2019
1,30-08-1996,20-09-2019,30-08-2019
2,28-02-1997,          ,31-01-2008
2,28-02-1997,          ,29-02-2008
2,28-02-1997,          ,31-03-2008
2,28-02-1997,          ,30-04-2008
2,28-02-1997,          ,30-05-2008
2,28-02-1997,          ,30-06-2008
2,28-02-1997,          ,31-07-2008
2,28-02-1997,          ,29-08-2008
3,01-10-2007,          ,30-10-2020
3,01-10-2007,          ,30-11-2020
3,01-10-2007,          ,31-12-2020
3,01-10-2007,          ,29-01-2021
3,01-10-2007,          ,26-02-2021
3,01-10-2007,          ,31-03-2021
3,01-10-2007,          ,30-04-2008
3,01-10-2007,          ,30-05-2008
;

[Kurt_Bremser]

This can be done with one simple assignment:

age = floor(intck('month',birth_date,ifn(death_date = .,date,death_date),"c");

If that does not exactly give you the desired result, omit the "c" and see then.

If both methods do not give you your desired result, post what you expect instead.

[saraphdnz]

ID	Birth_Date	Death_Date	Date	age_desired	age	age2
1	30/08/1996	20/09/2019	31/01/2019	269	276	277
1	30/08/1996	20/09/2019	28/02/2019	270	276	277
1	30/08/1996	20/09/2019	29/03/2019	271	276	277
1	30/08/1996	20/09/2019	30/04/2019	272	276	277
1	30/08/1996	20/09/2019	31/05/2019	273	276	277
1	30/08/1996	20/09/2019	28/06/2019	274	276	277
1	30/08/1996	20/09/2019	31/07/2019	275	276	277
1	30/08/1996	20/09/2019	30/08/2019	276	276	277
1	30/08/1996	20/09/2019	30/09/2019	277	276	277
1	30/08/1996	20/09/2019	31/10/2019	.	276	277
1	30/08/1996	20/09/2019	30/11/2019	.	276	277
1	30/08/1996	20/09/2019	30/12/2019	.	276	277
1	30/08/1996	20/09/2019	31/01/2020	.	276	277
2	28/02/1997	.	31/01/2008	131	131	131
2	28/02/1997	.	29/02/2008	132	132	132
2	28/02/1997	.	31/03/2008	133	133	133
2	28/02/1997	.	30/04/2008	134	134	134
2	28/02/1997	.	30/05/2008	135	135	135
2	28/02/1997	.	30/06/2008	136	136	136
2	28/02/1997	.	31/07/2008	137	137	137
2	28/02/1997	.	29/08/2008	138	138	138
3	1/10/2007	.	30/10/2020	156	156	156
3	1/10/2007	.	30/11/2020	157	157	157
3	1/10/2007	.	31/12/2020	158	158	158
3	1/10/2007	.	29/01/2021	159	159	159
3	1/10/2007	.	26/02/2021	160	160	160
3	1/10/2007	.	31/03/2021	161	161	161
3	1/10/2007	.	30/04/2008	6	6	6
3	1/10/2007	.	30/05/2008	7	7	7

/**WITH "C"**/
DATA WANT1;
SET WANT;
age = floor(intck('month',birth_date,ifn(death_date = .,date,death_date),"C"));
RUN;
/**WITHOUT "C"**/
DATA WANT2;
SET WANT1;
age2 = floor(intck('month',birth_date,ifn(death_date = .,date,death_date)));
RUN;

@KurtBremser

Thanks, I tried both of the codes and it worked when the death_date is missing. Please see the output desired age. I want age based on each month as appeared in the Date column. 

 Regards, Sara

[Kurt_Bremser]

So you want a minimum of the dates if death_date is not missing, so this is the calculation:

data want;
set have;
age = floor(intck('month',birth_date,ifn(death_date = .,date,min(date,death_date))));
run;

What is the rule for setting date_desired to missing in the last 4 observations of id 1?

[saraphdnz]

Thanks for your prompt response.

The ID 1 - last four missing values in age_desired is due to the death_date,
as the person is deceased so there is no further age calculation.

I tried the code you provided and can see the matched output (age-column) but I want blank cells when the person Death_Date is less than the Date column.

ID	Birth_Date	Death_Date	Date	age_desired	age
1	30/08/1996	20/09/2019	31/01/2019	269	269
1	30/08/1996	20/09/2019	28/02/2019	270	270
1	30/08/1996	20/09/2019	29/03/2019	271	271
1	30/08/1996	20/09/2019	30/04/2019	272	272
1	30/08/1996	20/09/2019	31/05/2019	273	273
1	30/08/1996	20/09/2019	28/06/2019	274	274
1	30/08/1996	20/09/2019	31/07/2019	275	275
1	30/08/1996	20/09/2019	30/08/2019	276	276
1	30/08/1996	20/09/2019	30/09/2019	277	277
1	30/08/1996	20/09/2019	31/10/2019	.	277
1	30/08/1996	20/09/2019	30/11/2019	.	277
1	30/08/1996	20/09/2019	30/12/2019	.	277
1	30/08/1996	20/09/2019	31/01/2020	.	277
2	28/02/1997	.	31/01/2008	131	131
2	28/02/1997	.	29/02/2008	132	132
2	28/02/1997	.	31/03/2008	133	133
2	28/02/1997	.	30/04/2008	134	134
2	28/02/1997	.	30/05/2008	135	135
2	28/02/1997	.	30/06/2008	136	136
2	28/02/1997	.	31/07/2008	137	137
2	28/02/1997	.	29/08/2008	138	138
3	1/10/2007	.	30/10/2020	156	156
3	1/10/2007	.	30/11/2020	157	157
3	1/10/2007	.	31/12/2020	158	158
3	1/10/2007	.	29/01/2021	159	159
3	1/10/2007	.	26/02/2021	160	160
3	1/10/2007	.	31/03/2021	161	161
3	1/10/2007	.	30/04/2008	6	6
3	1/10/2007	.	30/05/2008	7	7

[Kurt_Bremser · Accepted solution]

That would also exclude the observation with a date of 30/09/2019, as that is past the death_date. So I guess you want to have missing values when you are past the month of death_date.

Add another condition:

data have;
infile datalines dlm='09'x dsd truncover;
input id (birth_date death_date date) (:ddmmyy10.);
format birth_date death_date date yymmdd10.;
datalines;
1	30/08/1996	20/09/2019	31/01/2019
1	30/08/1996	20/09/2019	28/02/2019
1	30/08/1996	20/09/2019	29/03/2019
1	30/08/1996	20/09/2019	30/04/2019
1	30/08/1996	20/09/2019	31/05/2019
1	30/08/1996	20/09/2019	28/06/2019
1	30/08/1996	20/09/2019	31/07/2019
1	30/08/1996	20/09/2019	30/08/2019
1	30/08/1996	20/09/2019	30/09/2019
1	30/08/1996	20/09/2019	31/10/2019
1	30/08/1996	20/09/2019	30/11/2019
1	30/08/1996	20/09/2019	30/12/2019
1	30/08/1996	20/09/2019	31/01/2020
2	28/02/1997	.	31/01/2008
2	28/02/1997	.	29/02/2008
2	28/02/1997	.	31/03/2008
2	28/02/1997	.	30/04/2008
2	28/02/1997	.	30/05/2008
2	28/02/1997	.	30/06/2008
2	28/02/1997	.	31/07/2008
2	28/02/1997	.	29/08/2008
3	1/10/2007	.	30/10/2020
3	1/10/2007	.	30/11/2020
3	1/10/2007	.	31/12/2020
3	1/10/2007	.	29/01/2021
3	1/10/2007	.	26/02/2021
3	1/10/2007	.	31/03/2021
3	1/10/2007	.	30/04/2008
3	1/10/2007	.	30/05/2008
;

data want;
set have;
age = floor(intck('month',birth_date,ifn(death_date = .,date,min(date,death_date))));
if death_date ne . then if intck('month',death_date,date) ge 1 then age = .;
run;

Please supply your example data in this form (data step with datalines) in the future; it makes it much easier for us to develop and test code.

[saraphdnz]

@Kurt_Bremser 

Thanks, heaps for your help, I really appreciate that.

I will follow the protocol next time.

Regards,

Sara