topic Re: SAS reading in date as character 9, having trouble converting in SAS Programming

SAS reading in date as character 9, having trouble converting

Xamius32 — Mon, 05 Aug 2013 12:43:26 GMT

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?

Re: SAS reading in date as character 9, having trouble converting

Tom — Mon, 05 Aug 2013 12:50:24 GMT

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.

Re: SAS reading in date as character 9, having trouble converting

Xamius32 — Mon, 05 Aug 2013 12:54:35 GMT

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.

Re: SAS reading in date as character 9, having trouble converting

Tom — Mon, 05 Aug 2013 13:30:23 GMT

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.

Re: SAS reading in date as character 9, having trouble converting

Fugue — Mon, 05 Aug 2013 20:08:38 GMT

Are the errors showing up only on certain records, or the entire column?