Problem importing dates in SAS

Reply
Regular Contributor
Posts: 183

Problem importing dates in SAS

Hi Experts,

The following is a sample from my excel file. When I import this file to SAS, SAS shows numbers instead of dates. I want to have the date format like ddmmyy10. in SAS. Please note that the following formula is written in excel cell for the first Filing_date:

=DATE(2000,1,1)

Filing_date    Issue_date

01-01-0001-01-00
13-12-9904-01-00
12-10-9905-01-00
28-06-9907-01-00
07-01-0007-01-00
19-11-9910-01-00
10-01-0010-01-00
11-01-0011-01-00
Respected Advisor
Posts: 3,065

Re: Problem importing dates in SAS

Apply a SAS format to the date column like DDMMYY10. using a FORMAT statement:

data want;

  set have;

  format Filing_date    Issue_date DDMMYY10.;

run;

Regular Contributor
Posts: 183

Re: Problem importing dates in SAS

I did that but it shows 02/01/2060 for the first filing date, for example and so on.

Respected Advisor
Posts: 3,065

Re: Problem importing dates in SAS

Thats because Excel stores dates as the number of days since 1 Jan 1900 and SAS stores dates as the number of days since 1 Jan 1960.

You are probably importing the column as an Excel number or general type - try changing the column type in Excel to date and then import again

Esteemed Advisor
Posts: 6,685

Re: Problem importing dates in SAS

Or, when importing Excel dates as raw numbers, subtract '01jan1960'd - '30dec1899'd from the SAS date after the input.

(Excel nominally starts counting at 01-01-1900, but since it has an unsolved bug that causes it to make 1900 a leap year(!!), you need another day in SAS (which is bug-free in this context, of course))

So you need to define day "1" as 31dec1899 when dealing with Excel dates.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: Problem importing dates in SAS

Or, don't use Excel, use a proper data transfer format. 

Regular Contributor
Posts: 183

Re: Problem importing dates in SAS

Excel is the raw file. When I download the data from the database, it downloads in Excel.

Esteemed Advisor
Posts: 6,685

Re: Problem importing dates in SAS

Make it export in another format. All RDBMS's can export to flat files.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Regular Contributor
Posts: 183

Re: Problem importing dates in SAS

Could you please explain little more? Which format will work?

Esteemed Advisor
Posts: 6,685

Re: Problem importing dates in SAS

Anything that is readable with a simple text editor, like .csv (comma separated values); or fixed-width text files, where each column is always padded to the maximum size.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: Problem importing dates in SAS

As KurtBremser has pointed out databases should allow export to a number of formats which are data transfer compliant, CSV, XML, database dump.  You should also be able to directly integrate with databases, for example by ODBC.  You would need to check what certain connection stings are needed with your DB admin, and then you could do something like:

proc sql;

     connect to db (path="...." schema="..." ...);

     create table WANT as

     select * from connection to db (select * from atable);

     disconnect from db;

quit;

This would connect to the database directly and retrieve the data.  This type of code is called pass through, you can also libname directly to a database in later versions of SAS. 

Do note however that I assume your using proc import to read the Excel file.  Again, I wouldn't recommend that.  The reason is that proc import is a guessing procedure.  It does a quick check of your data and guesses what you want to import.  Better to write a datastep to read in the data exactly as you want it:

data want;

     infile "abc.csv" dlm=",";

     length var1 $200 var2 $20;

     informat var1 ...;

     format var1 ...;

     input var1 $ var2 $ ...;

run;

That way you tell SAS what informat to read the data, what format to apply to the data, what lengths etc.

Regular Contributor
Posts: 183

Re: Problem importing dates in SAS

How will I subtract? It will be great if you write the code. I was trying, but it's not working.

Esteemed Advisor
Posts: 6,685

Re: Problem importing dates in SAS

Look at this log:

15     data _null_;
16     format date ddmmyyp10.;
17     date = 36526; * this is the Excel number for Jan 1st, 2000;
18     put date=;
19     date = date - ('01jan1960'd - '30dec1899'd);
20     put date=;
21     run;

date=02.01.2060

date=01.01.2000

NOTE: DATA statement used (Total process time):

  real time       0.00 seconds
  cpu time        0.00 seconds
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 12 replies
  • 412 views
  • 0 likes
  • 4 in conversation