problems with calculating age

[noetsi]

I am using the most recent version of Enterprise Guide (7.1).

I have a function I have used for years successfully to calculate age. It's

((floor ((intck('month',

T1.DATEOFBIRTH,

T1.DATEBEGIN_AT_CLO) - (day (T1.DATEBEGIN_AT_CLO) < day (T1.DATEOFBIRTH)))/12) )
)

Where I want to calculate age at datebegin_At_Clo .The problem here is that I am starting with a date stored as a text field (I did not generate it the federal government does and I do not know how they formatted it except that it is a string).

It is of the form '19761026' where the first four digits are the year, the next two the month and the next two the days.

I ran

PROC SQL;
CREATE TABLE WORK.QUERY_FOR_Q16 AS 
SELECT t1.DateOfBirth, 
t1.ClosureDate, 
/* DOB */
(INPUT(t1.DateOfBirth, yyyymmdd10.)) LABEL="DOB" AS DOB, 
/* CD */
(INPUT(t1.ClosureDate, yyyymmdd10.)) LABEL="CD" AS CD
FROM WORK.Q16 t1;
QUIT;

and it appears to generate a number fine. However when I use the new fields (DOB and CD) in the age function above I get no results (not errors just all missing data).

That is I get missing data when I run

PROC SQL;
CREATE TABLE WORK.QUERY_FOR_Q16_0000 AS 
SELECT t1.DateOfBirth, 
t1.ClosureDate, 
/* Age */
(((floor ((intck('month',

T1.Dob,

T1.cd) - (day (T1.cd) < day (T1.Dob)))/12) )
)
) LABEL="Age" AS Age
FROM WORK.QUERY_FOR_Q16 t1;
QUIT;

** Edited to use CODE boxes.

[ballardw]

everywhere you need that text variable you should be able to use

input(t1.variablename,yymmdd8.) to convert the text into a SAS data value

such as

Day(input(t1.datebegin_At_Clo ,yymmdd8.) )

I must say, just because the feds do doesn't mean you have to persist. You could have read your local version of the data into a SAS date value.

Since I have caught CDC doing some very poor calculations with character values improperly converted to numerics using string functions I watch such things. (We had a person with a height of 11inches because CDC parsed, without catching, a value of 5011 which would have meant 50 feet 11 inches using SUBSTR)

[Tom]

I suspect that you actually read the string like '20170414' as a NUMBER and then assigned it to a character variable and so SAS used the default format of BEST12. and so stored '    20140414' into the variable that you are trying to convert to a date. The leading spaces are causing trouble, but can be hard to see since normally SAS removes them when you PUT the value.

Add a LEFT() or STRIP() function call and see if it helps.

,input(left(datechar),yymmdd8.) as dob format=yymmdd10. label='Date of Birth'

[art297]

The following works for me:

data q16;
  informat DateOfBirth ClosureDate $10.;
  input DateOfBirth ClosureDate;
  cards;
19761026 20170414
19440415 20170414
;
PROC SQL;
  CREATE TABLE QUERY_FOR_Q16 AS 
    SELECT INPUT(DateOfBirth, yymmdd8.) LABEL="DOB" AS DOB, 
      /* CD */
      INPUT(ClosureDate, yymmdd8.) LABEL="CD" AS CD,
      yrdif(calculated DOB,calculated CD,'AGE') as age
         FROM Q16
  ;
QUIT;

Art, CEO, AnalystFinder.com