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?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.