topic Re: Reading dates from excel in New SAS User

Reading dates from excel

sasuser1031 — Mon, 16 Sep 2019 21:08:49 GMT

I have date format 16-Oct in excel. How can I import in date format in SAS?

For example:

data a;
infile "&input.&data." delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
informat date yymmdd6. ;
format date yymmdd6. ;
input date;
run;

This code gives missing values in date column.

Thanks.

Re: Reading dates from excel

Tom — Mon, 16 Sep 2019 21:15:20 GMT

Do you have an Excel file or a text file? You cannot read an Excel file as if it was a text file. To read from an Excel file you will need to use PROC IMPORT or use LIBNAME statement to point at the workbook and read the sheets out as datasets.

If you do have a text file then you need a different informat. The YYMMDD informat expects to have numeric Year, Month and Day values, in that order. Try using the ANYDTDTE. informat instead. Otherwise read it as a string and convert the string into a format that one of the SAS date informats can read.

Re: Reading dates from excel

sasuser1031 — Mon, 16 Sep 2019 21:19:59 GMT

It's excel file. There are more than 200 variables and more than 10 date columns. Other date formats read well, but this date format is not working. I also tried using ANYDTDTE but didn't work.

Re: Reading dates from excel

Tom — Mon, 16 Sep 2019 21:26:57 GMT

What date do you think 16-Oct represents? It is the 16th of October of the current year? Is any day in in October of 2016? Or perhaps any day in October of 1916?

Re: Reading dates from excel

sasuser1031 — Mon, 16 Sep 2019 21:29:37 GMT

It is October of 2016.

Re: Reading dates from excel

Reeza — Mon, 16 Sep 2019 21:33:39 GMT

You cannot specify the types when importing Excel data.

When you import it, what does it look like? You'll need to convert it from Excel to a SAS date, most likely character to date?

Re: Reading dates from excel

ballardw — Mon, 16 Sep 2019 22:26:35 GMT

@sasuser1031 wrote:

It is October of 2016.

I suggest going into Excel, finding that cell and using the Format Cells option set that cell to a format that shows day, month and year. Every value I have seen in Excel that is an actual date that looks like 16-Oct is 16 Oct of the current year when entered. Month and year normally appear as Oct-16. If you have created a custom date appearance that wouldn't be the case but I see very few people do that.

And perhaps set the entire column the selected the date format. You might find that your "dates" are actually character or possibly even worse a mixture of character and Excel date values.