08-05-2015 10:59 PM
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:
08-06-2015 12:46 AM
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
08-06-2015 02:39 AM
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.
08-06-2015 07:33 AM
Make it export in another format. All RDBMS's can export to flat files.
08-06-2015 07:43 AM
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.
08-06-2015 09:22 AM
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:
connect to db (path="...." schema="..." ...);
create table WANT as
select * from connection to db (select * from atable);
disconnect from db;
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:
infile "abc.csv" dlm=",";
length var1 $200 var2 $20;
informat var1 ...;
format var1 ...;
input var1 $ var2 $ ...;
That way you tell SAS what informat to read the data, what format to apply to the data, what lengths etc.
08-07-2015 02:32 AM
Look at this log:
|16||format date ddmmyyp10.;|
|17||date = 36526; * this is the Excel number for Jan 1st, 2000;|
|19||date = date - ('01jan1960'd - '30dec1899'd);|
NOTE: DATA statement used (Total process time):
|real time||0.00 seconds|
|cpu time||0.00 seconds|