Change the date format (weird data)

[takpdpb7]

I extracted a dataset and in the database, the DOB is showing normally for all individuals (01/01/2000), but in the excel extract, the date is showing as a series of numbers, like 111111. For example, the DOB for one individual is 08/18/1934, but in the excel extract, it is 12649. I'm not seeing any correlation between the two numbers. What date format could I do to correct this in SAS?

[ed_sas_member]

Hi @takpdpb7 

The reference date used in Excel is 01JAN1900.

12649 formatted in Excel results in this:

 

In SAS, the reference date is different: it is actually 01JAN1960.

One way to handle the problem is to do this:

data have;
date = "30DEC1899"d + 12649;
format date date9.;
run;

Best,

[Reeza]

What happens if you take the number in Excel and apply a date format to it?

Most applications actually store dates and times as numbers and then apply a display format to show it as a time. So what you're seeing is likely the unformatted value.

Also, how you export your data to Excel matters. How are you doing that step? PROC EXPORT doesn't typically write labels and formats by default but ODS EXCEL does.

ods excel file='/folders/myfolders/demo.xlsx';

proc print data=sashelp.stocks;
where stock = 'IBM';
run;

ods excel close;

---

@takpdpb7 wrote:

I extracted a dataset and in the database, the DOB is showing normally for all individuals (01/01/2000), but in the excel extract, the date is showing as a series of numbers, like 111111. For example, the DOB for one individual is 08/18/1934, but in the excel extract, it is 12649. I'm not seeing any correlation between the two numbers. What date format could I do to correct this in SAS?

---

[Tom]

You need to more clearly specify what you did to get a detailed answer.  But the number you are seeing looks like how Excel would normally store that date when it is using the default base year of 1900.  In SAS that number would be the date 19AUG1994.

1950  data _null_;
1951    have=12649 ;
1952    sasdate=have + '30DEC1899'd ;
1953    put (2*have 2*sasdate) (= comma. date9. /);
1954  run;

have=12,649 19AUG1994
sasdate=-9,267 18AUG1934

[takpdpb7]

I just used the export function on the database I am working with and the raw data was showing 12649. I then used proc import to bring the dataset into SAS. 

proc import out=BAB
datafile="path"
dbms=excel
replace;
run;

[Reeza]

Are you having issues displaying your data in Excel or after you've imported it to SAS from Excel and are trying to work with it in SAS?

[Reeza]

Ideally you would connect SAS directly do your DB so you avoid any data type issues when importing/exporting the data.

[ballardw]

Go to the Excel file, highlight the column and change the data type to one of the date formats you like.

Excel starts counting dates at 1 Jan 1900 and that is likely to be the number of days since that day.

Is this Excel file output from SAS or something you read? If it is output you should show us how you are creating the output to see if we can make this "transparent", not an additional step.