So I am importing from excel a date variable which is 1/1/2011 format. SAS reads it in as a character 9 format. When I try to convert it, it always gives me errors. Ive tried doing input(date,f9.0), creating a new variable called newdate, formatting it as date9. (I want it as date9) and then saying newdate=date, etc.
Any ideas?
You should use a date format for the INPUT() function.
newdate = input(date,mmddyy10.);
format newdate date9. ;
But 9 characters is not enough to store data in MM/DD/YYYY format. If you have data with two digit month and two digit day you will lose the last character of the year.
Excel has it as 1/1/2001 and it never gets bigger than 9 because the day is always the 1st. It is interesting that it actually works for 2 of the files I have, with the excel formatting being the exact same, and from what I can see no inconsitincies in the actual data.
Usually import from Excel will do this because there are missing values that make the cell look like character rather than number. Part of the problem with using Excel as your data source. Spreadsheets allow each cell to be independently typed, but SAS and databases require that columns (variables) be of a consistent variable type.
Are the errors showing up only on certain records, or the entire column?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.