BookmarkSubscribeRSS Feed
sasuser1031
Calcite | Level 5

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.

6 REPLIES 6
Tom
Super User Tom
Super User

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.

sasuser1031
Calcite | Level 5

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. 

Tom
Super User Tom
Super User

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?

sasuser1031
Calcite | Level 5

It is October of 2016. 

ballardw
Super User

@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.

Reeza
Super User
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?

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 1175 views
  • 0 likes
  • 4 in conversation