How do I use informat and format on dates

[Lety08]

I Have data from a xls file that has a column with date as 20081113 where it is the year, month and day.  I want to change that date, but I keep getting either the wrong date, dots or stars. Following some answers in here I came out with this code, but I just get a dot

data auction.clean3;
set auction.clean2;
DATE= input(PUT(S_SALE_DATE, YYMMDDn8.),anydtdte8.);
format DATE DATE10.;
attrib C_BID_AMOUNT format=dollar14.2;
DROP S_SALE_DATE;
run;

could anyone help me on what could be wrong?

[ballardw · Accepted solution]

If the value is currently numeric of 20081113 and a format like BEST12. , 12. , F12 (or 32 or 8 for any of those digits then it is not a "date" at all and when you Put the value with any date related format you are effectively attempting to treat the value as that number of days after 1 Jan 1960. Which results in a value way past the SAS date limit of 31 Dec 20000 (yes 18,000 years in the future) which is currently as far as SAS treats values for dates.

So try this:

data example;
   number=20081113;
   date = input(put(number,8. -L),yymmdd8.);
   format date date9.;
run;

the -L in the put just makes sure that the value from the put is Left justified just in case you use a format longer than 8 to create the text value for input.

However you should run proc contents on your data before running the step to make sure the value is 1)numeric and 2) does not already have some format like yymmdd associated. Depending on how you read the file the value might already be a date.

Dot = Missing data and you would have had an "invalid data" message in the log with some note about the invalid value.

* means the "value" can't be displayed with the format chosen. With "dates" that usually means you have a year value greater than 9999 which is the most that SAS will display. Yes I said you can have values up to year 20000. But SAS doesn't have any format to display anything after 31DEC9999. Not really expecting much actual use for year 10K yet.