Converting dates: from character to numeric, from Excel to SAS
I’m back with another SAS Tip of the Day. Like a lot of people, I work with dates very often.
- How many days is it from when a client applies to when he or she is determined eligible?
- How many days until the average client is employed?
You get the idea. Inconveniently, in this particular case, I received the data in an Excel file and when I uploaded the file all of the dates were in character format. Here is a simple fix.
- Create an array of the character dates. Takes one statement. Note that you need that $ to indicate character variables.
- Create an array of your numeric dates. Takes one statement. Leave OFF the $ to indicate these are NOT character variables.
- Use a DO loop to fix any data problems, read into the new numeric variable and subtract 21,916. This is the number of days difference between the reference date for SAS and for Excel. You can read more about that here.
- Not required but good practice , since I was not going to use the character date values, I dropped those from the data set as well as the j subscript variable.
data fixdata ;
set fix1;
array chardates {9} $ birthdate date_app date_assess date_eligible date_ipe date_closed date_employ date_int_completed date_last_contact
;
array numdates {9} date_birth app_date assess_date eligible_date ipe_date closed_date employ_date
int_completed_date last_contact_date
;
** Change all date variables to exclude invalid dates ;
** And from Excel to SAS date format ;
do j = 1 to 9 ;
if chardates{j} = "0000-00-00" then numdates{j} = . ;
else numdates{j} = chardates{j} - 21916 ;
end;
drop j birthdate date_app date_assess date_eligible date_ipe date_closed date_employ date_int_completed date_last_contact;
I live in opposite world. I blog on SAS and statistics for fun and make games for a living. Check out Making Camp Premium. Learn about Ojibwe culture, brush up your math skills, learn more English and have fun. All for under two bucks.
