BookmarkSubscribeRSS Feed
takpdpb7
Calcite | Level 5

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?

7 REPLIES 7
ed_sas_member
Meteorite | Level 14

Hi @takpdpb7 

The reference date used in Excel is 01JAN1900.

12649 formatted in Excel results in this:

Capture d’écran 2020-06-08 à 18.26.53.png

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

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
Super User Tom
Super User

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
Calcite | Level 5

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

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
Super User
Ideally you would connect SAS directly do your DB so you avoid any data type issues when importing/exporting the data.
ballardw
Super User

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.

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

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
  • 7 replies
  • 1574 views
  • 1 like
  • 5 in conversation