Date conversion when importing a file

[ab97_cd]

Hi,

I want to import data with columns "shop", "id", "2019-01-01", "2019-02-01", "2019-03-01", "2019-04-01". I have problem with date because SAS read it as "42455" etc. How can I fix it?

[Kurt_Bremser]

Save from Excel as csv, read with a data step where you use proper column names, then immediately transpose to a long format. After the transposition, convert the _name_ column to a SAS date column.

[Tom]

---

@ab97_cd wrote:

Hi,

I want to import data with columns "shop", "id", "2019-01-01", "2019-02-01", "2019-03-01", "2019-04-01". I have problem with date because SAS read it as "42455" etc. How can I fix it?

---

Variable names are character strings, not dates or numbers.  So if you tried to read an Excel file where the variable names were entered into the cells as dates instead of as strings then SAS will convert them into strings.

When SAS reads a date value from Excel as a string it gets the underlying number that Excel uses for that date converted into a character string.  To convert back to a date first convert them to a number and then add the negative value that SAS uses for the base date that Excel uses for dates.  Excel numbers from 1900 and SAS from 1960 but there is an additional 2 day difference because of difference in whether to count from zero or one and because Excel mistakenly treats 1900 as a leap year.

date = input(date_string,32.)+'30DEC1899'd ;
format date yymmdd10.;