convert birthdate to age

[tmm]

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.

[art297]

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

[tmm]

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

[art297]

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.

[Patrick]

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-... 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%2...

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?

[Hima]

Have you tried some thing like this?

DATA TEMP;

FORMAT START_DT MMDDYY10.;

START_DT=TODAY()-218;

RUN;

PROC PRINT; RUN;

   Obs      START_DT

     1     07/31/2011

DATA TEST;
INPUT DT_BRTH MMDDYY10.;
DATALINES;
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
;
RUN;

%LET START_DT=TODAY()-218;

PROC SQL;

SELECT DT_BRTH FORMAT MMDDYY10., (&START_DT. - DT_BRTH)/365.25 AS AGE FROM TEST;

QUIT;

DT_BRTH       AGE
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
                            

02/27/1933 78.42026

04/28/1943 68.25736

01/14/1936 75.54278

12/23/1949 61.60164

05/29/1940 71.17043

09/30/1946 64.83231

12/12/1959 51.63313

01/19/1959 52.52841

03/03/1944 67.40862

08/20/1944 66.94319