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
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.
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
;
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.
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;
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
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?
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 |
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.
Thanks, heaps for your help, I really appreciate that.
I will follow the protocol next time.
Regards,
Sara
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.