topic Date conversion when importing a file in SAS Data Management

Date conversion when importing a file

ab97_cd — Mon, 09 Dec 2019 13:15:39 GMT

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?

Re: Date conversion when importing a file

Kurt_Bremser — Mon, 09 Dec 2019 13:27:34 GMT

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.

Re: Date conversion when importing a file

Tom — Mon, 09 Dec 2019 15:20:56 GMT

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