BookmarkSubscribeRSS Feed
noetsi
Obsidian | Level 7

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.

3 REPLIES 3
ballardw
Super User

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
Super User Tom
Super User

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
Opal | Level 21

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 973 views
  • 0 likes
  • 4 in conversation