topic convert birthdate to age in SAS Procedures

convert birthdate to age

tmm — Fri, 02 Mar 2012 19:12:13 GMT

I am trying in SAS to convert my column name BTH_DT to an age and used this code:

bth_dt = int(datediff("m",[bth_dt],date())/12)

this is contained in my proc sql statement which is

proc sql;

(select distinct

memberid,

fst_srvc_dt,

bth_dt = int(datediff("m",[bth_dt],date())/12),

member_gender_cd

from inpatient

rest of code

the entire code works great if I take out the converting to age. It will give me all columns and the bth_dt column. but when i add this code in I get an error

something expected between bth_dt and = just not sure how to code this quite right

Re: convert birthdate to age

HB — Fri, 02 Mar 2012 19:32:33 GMT

You might try

proc sql;

select distinct

memberid,

fst_srvc_dt,

int(datediff("m",[bth_dt],date())/12) as bth_dt,

intck("YEAR", bth_dt, date(),"CONTINUOUS") as memberAge,

member_gender_cd

from inpatient;

UPDATED with information from PGSTATS below.

convert birthdate to age

Haikuo — Fri, 02 Mar 2012 19:36:07 GMT

This topic definitely belongs to FAQ, it could have so many different answers.You can find one of recent discussions from SAS-L @:

http://listserv.uga.edu/cgi-bin/wa?A2=ind1201B&L=sas-l&P=R13704#TOP

Regards,

Haikuo

convert birthdate to age

PGStats — Fri, 02 Mar 2012 19:38:56 GMT

It seems the dateDiff has been replaced with intck. You would need intck("YEAR", bth_dt, date(),"CONTINUOUS") as memberAge in your query.

convert birthdate to age

tmm — Fri, 02 Mar 2012 19:44:12 GMT

I get the error from intick as: data type "year" does not match a defined type name

convert birthdate to age

tmm — Fri, 02 Mar 2012 19:46:06 GMT

sorry I did put in intck not intick but still the same error applies

convert birthdate to age

PGStats — Fri, 02 Mar 2012 19:56:48 GMT

If bth_dt is a SAS DATETIME value and not a SAS DATE value then you should use "DTYEAR" as the time unit in intck and DATETIME() instead of DATE().

convert birthdate to age

tmm — Fri, 02 Mar 2012 20:07:58 GMT

BTH_DT is not a SAS date or SAS Datetime

It is the birthdate of the member in the DB2 table. I am connecting to our DB2 server using an ODBC. The BTH_DT is a predefined column in our members table that we have insurance coverage.

convert birthdate to age

PGStats — Fri, 02 Mar 2012 20:14:41 GMT

Normally, dates are translated properly between other DBMS and SAS. When you receive a date in SAS, it is a proper SAS DATE or a proper SAS DATETIME. This is my experience but I don't know about DB2. - PG

convert birthdate to age

tmm — Fri, 02 Mar 2012 20:18:21 GMT

ok. Well I did try it and still getting errors. I will see if my boss knows the answer or maybe someone on our SAS team

convert birthdate to age

tmm — Fri, 02 Mar 2012 20:19:05 GMT

Just hate going those routes cuz our SAS team takes weeks to respond to anything

convert birthdate to age

PGStats — Fri, 02 Mar 2012 20:21:20 GMT

Well, give us your last try and the log, maybe only a small error left. - PG

convert birthdate to age

tmm — Fri, 02 Mar 2012 20:29:06 GMT

intck("dtyear", bth_dt, datetime(), "CONTINUOUS") as memberage

the error log says:

syntax error: data type "DTYEAR" does not match a defined type name

convert birthdate to age

PGStats — Fri, 02 Mar 2012 21:10:28 GMT

Strange, "DTYEAR" is an interval name, not a "defined type name". The error might have occured elsewhere in the query.

Anyhow, Time Interval functions are a bit new to SAS (I'm using version 9.3). So, you might try

intck("YEAR", datepart(bth_dt), DATE(), "C")

or maybe

dateDiff("y",datepart(bth_dt), DATE()) /* If there really is a function called dateDiff in SAS */

or else, go for the close approximation

int((datepart(bth_dt) - date())/365.25)

convert birthdate to age

tmm — Fri, 02 Mar 2012 23:00:23 GMT

I am on 9.2 SAS

convert birthdate to age

tmm — Fri, 02 Mar 2012 23:09:39 GMT

Man this is still not working. It is driving me nuts. I might just have to keep the bth_dt part in the table and maybe try and figure out a way to convert in a separate query or something.

convert birthdate to age

art297 — Fri, 02 Mar 2012 23:36:38 GMT

Can you just do a proc sql and select bth_dt for about 10 records and post the resulting file here?

convert birthdate to age

Patrick — Sat, 03 Mar 2012 01:43:09 GMT

Agree with Art that it would be helpful to actually know how the values are stored on the DB side and how they come through to SAS (data type in DB, data type in SAS - and if it's a string then how the string looks like).

The error you get looks to me like thrown by the database and not by SAS (at least I couldn't find a SAS error message like this).

I was Googling your error message: data type does not match a defined type name

Interestingly I've got hits for Teradata http://datawarehouse.ittoolbox.com/groups/technical-functional/teradata-l/failure-3706-syntax-error-data-type-xyz-does-not-match-a-defin-ed-type-name-2675934 but not for DB2.

I also couldn't find a DB2 SQL error code/message like the one you've got when searching here: http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.codes%2Fsrc%2Ftpc%2Fdb2z_n.htm

Are you sure your source table is in DB2 and not in Teradata?

Could you please re-run your code after below options (once just selecting the column as Art asks for, once with the intck() function) and then post the log part which shows what SQL has been sent to the database?

OPTIONS sastrace=',,,d' sastraceloc=saslog nostsuffix;.

Also: How are you running your code? Is it eventually using SAS table metadata definitions?

convert birthdate to age

tmm — Mon, 05 Mar 2012 16:37:14 GMT

This is a teradata database and when I am coding the date lookup in my %let statement I cannot do it as '7/20/2011' I have to code it as %let startdt=31072011

BTH_DT

2/27/1933

4/28/1943

1/14/1936

12/23/1949

5/29/1940

9/30/1946

12/12/1959

1/19/1959

3/3/1944

8/20/1944

convert birthdate to age

art297 — Mon, 05 Mar 2012 17:06:41 GMT

I presume the bth_dt list you showed was the result of a proc sql run just selecting the first 10 records. One more bit of info needed then. Do the same thing but with a create table statement in the run and then let us know the variable time that results in the created table.