topic Re: Number of months between testdate and birthdate? in SAS Programming

Number of months between testdate and birthdate?

Alopex — Wed, 04 Nov 2015 10:06:51 GMT

I have testdates and birthdates in format 20070315 - 20021021. I would like a column with age at testing in months, but hit a wall when trying to convert these dates into SAS-dates..

I have tried

sas_testdate=testdate;

format sas_testdate yymmdd10.;

sas_birthdate=birthdate;

format sas_birthdate yymmdd10.;

ageattest=(sas_testdate-sas_birthdate)/365/30;

run;

but it seems this code is far off 😉

And if there exist a way to convert into months without dividing on days, it would be great!

Re: Number of months between testdate and birthdate?

RW9 — Wed, 04 Nov 2015 10:34:45 GMT

You will want to review the SAS documentation on the INTCK function:

http://support.sas.com/documentation/cdl/en/lefunctionsref/63354/HTML/default/viewer.htm#p1md4mx2crzfaqn14va8kt7qvfhr.htm

months_between=intck('month',sas_birthdate,sas_testdate);

Re: Number of months between testdate and birthdate?

Alopex — Wed, 04 Nov 2015 10:53:11 GMT

Thank you, that was what I was looking for (I tried the SAS support pages, but didn't find this one).

But it is still something wrong with my code which leads to the sas-dates not being processed?!

Re: Number of months between testdate and birthdate?

RW9 — Wed, 04 Nov 2015 11:16:21 GMT

Can you post an example of your data (in the form of a datastep) - match it exatly to your data, but just a couple of test lines. I suspect that in your data you may have datetime variables, or character dates or something like that (do a proc contents on the data, that would help show your data). The intck requires both values to be date values, so you would need to datepart() datetime variables for instance, but I can't tell without seeing the data.

Oh, and maybe post the code you are using to process that line of code.

Re: Number of months between testdate and birthdate?

DartRodrigo — Wed, 04 Nov 2015 11:34:01 GMT

Hi mate,

First of all check out this SAS Date, Time, and Datetime Values.

There are some tricks to do this in Calculate number of years, months, and days between two dates

Or try YRDIF Function

Or you can test this program using intck function to get what you need:

data have;
 length date_1 $18 date_2 $8;
 infile datalines delimiter=','; 
 input date_1 $ date_2 $;

 datalines;
10FEB2010:00:00:00,02/07/08
10FEB2010:00:00:00,03/10/08
10FEB2010:00:00:00,04/07/08
24MAY2010:00:00:00,05/09/08
24MAY2010:00:00:00,06/11/08
24MAY2010:00:00:00,07/11/08
;

data want (drop=date_1 date_2 dt);
 set have;
 dt=input(date_1,datetime.);
 date1=datepart(dt);
 date2=input(date_2,mmddyy10.);

 month_from=intck('month',intnx('month',date1,0,'b'),intnx('month',date2,0,'b'));
 format date1 date2 mmddyy10.;
run;

Hope this helps

Re: Number of months between testdate and birthdate?

FreelanceReinh — Wed, 04 Nov 2015 11:40:07 GMT

For the conversion from YYYYMMDD format to SAS date values you will need the INPUT function. Please note that SAS date values are measured in days (with 1st Jan 1960 being day 0). Your FORMAT statements are useful if the date values are to be displayed (later), but they don't affect the values stored in your variables. Hence, together with the difference calculation as suggested by RW9, your code may essentially look like this:

data want;
set have;
sas_testdate  = input(testdate,  yymmdd8.);
sas_birthdate = input(birthdate, yymmdd8.);
ageattest=intck('month', sas_birthdate, sas_testdate);
run;

This assumes that TESTDATE and BIRTHDATE are character variables containing values such as '20070315'.

Re: Number of months between testdate and birthdate?

Alopex — Wed, 04 Nov 2015 11:46:02 GMT

You lost me at datastep ;D

I ran proc contents, and both testdate and birthdate is numeric. Birthdate was provided with the dataset, testdate was found from this:

Testdat = substr(Tilfelle, 1, 9 )*1;

Tilfelle is first column, testdate second.

20070325038 20070325
20090521032 20090521
20070331032 20070331

This is the closest I get, I at least get a result 😉 But neither sas-date exist, only . and ******, and so there is no testalder (age at test) either. But I'll change that code into your suggestion when I get the sas-dates going!

Testdate_sas=Testdate;

format testdate_sas yymmdd10.;

fodtdate_sas=fodt;

format fodtdate_sas yymmdd10.;

testalder=((testdate_sas-fodtdate_sas)/365/30);

run;

Re: Number of months between testdate and birthdate?

Alopex — Wed, 04 Nov 2015 11:48:08 GMT

No, both numeric.. But in form 20070315.

Re: Number of months between testdate and birthdate?

DartRodrigo — Wed, 04 Nov 2015 11:48:14 GMT

Hi Alopex, try using:

month_from=intck('month',intnx('month',testdate_sas,0,'b'),intnx('month',fodtdate_sas,0,'b'));

Hope this helps

Re: Number of months between testdate and birthdate?

RW9 — Wed, 04 Nov 2015 12:01:36 GMT

The point is to get your variables to be dates. This is a numeric variable which reflects the number of days since a certain date. Once you have that, then just use the intck function.

data have;
  test_date=20070325; dob=19800106;
run;

data want;
  set have;
  real_date_test_date=input(put(test_date,$8.),yymmdd8.);
  real_date_dob_date=input(put(dob,$8.),yymmdd8.);
  diff_in_months=intck('month',real_date_dob_date,real_date_test_date);
run;

Re: Number of months between testdate and birthdate?

FreelanceReinh — Wed, 04 Nov 2015 12:35:54 GMT

I support RW9's suggestion, except that the format used in the second argument of the PUT function should be 8. instead of $8.

Furthermore, please note that your code substr(Tilfelle, 1, 9) results in a 9- not 8-character string. Without the "*1" you'd directly obtain a character value for TESTDAT (which I would prefer). Dividing by 365 is definitely not necessary. Dividing a number of days by 30 comes close to the corresponding number of months. Unlike the INTCK function this approach would allow for non-integer results (which are only approximations, though, since not all months have exactly 30 days).

Re: Number of months between testdate and birthdate?

Alopex — Wed, 04 Nov 2015 12:59:26 GMT

Thank you, I think that will help! Ok, I'll change the substr. I just played around with it until the correct numbers showed up in the output data, but when you explain it, I see how it works!

Edit to say that since the column "tilfelle" from which I substr is numeric with a lot of ekstra spaces I think, so I need to use the original 1, 9 substr to get 20071221. No - used 2, 8 😄

And I tried RW9's method, but then got the log saying that I already defined a numeric variable or something like that, and I tried to remove it but that didn't work either. Will start over!

Re: Number of months between testdate and birthdate?

RW9 — Wed, 04 Nov 2015 13:20:31 GMT

Yes, I was on my way out to lunch, so didn't test it. Just change the format:

data have;
  test_date=20070325; dob=19800106;
run;

data want;
  set have;
  real_date_test_date=input(put(test_date,8.),yymmdd8.);
  real_date_dob_date=input(put(dob,8.),yymmdd8.);
  diff_in_months=intck('month',real_date_dob_date,real_date_test_date);
run;