04-14-2017 06:37 PM - last edited on 04-14-2017 07:30 PM by Tom
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.
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.
04-14-2017 07:07 PM
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
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)
04-14-2017 07:44 PM
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'
04-14-2017 07:47 PM
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