Importing date-time variables from Excel into SAS

[Mercator]

I do a lot of studies involving hospitalized patients. Thus I have a number of date-time variables, e.g. admission date-time. When I import an Excel spreadsheet into SAS the time part of the date-time variables gets stripped off. Is there a way to import data from Excel in which the date-time variables stay intact?

Thanks!

Andrew

[mlamias]

If you provided examples of some of the datetime formats you see in Excel, I'm sure you'll receive an answer, but it's difficult to assit without being able to reproduce your problem.  It would also be important to know how your are importing your data.  What version of SAS are you using?

[Mercator]

I'm sorry. Here's an example of the date-time formats in Excel:

HADMDTTM	IADMDTTM
09/24/2012 08:50:00	09/24/2012 14:56:00
10/07/2012 10:46:00	10/07/2012 10:49:00
09/09/2012 10:39:00	09/09/2012 12:23:00
08/01/2012 11:36:00	08/03/2012 23:06:00
10/06/2012 22:30:00	10/08/2012 14:46:00
08/12/2012 15:40:00	08/12/2012 19:18:00

[mlamias · Accepted solution]

I'm not sure which version of SAS or Excel you are using.  However, I'm running SAS 9.3 with Excel 2010 (with the xlsx file format).  Here is how this can be accomplished:

/*libname to excel file.  I just placed your example in the root of c*/

libname  xlsxfile "c:\exceldatetimefile.xlsx";

/*I named the output dataset sheet1.  You might want to call it something else.

The dbSASType dataset option is the critical part here.  You need to specify to

have SAS read your datetime variables in Excel as datetime format in SAS*/

data sheet1;

set xlsxfile."Sheet1$"n(dbSASType=(HADMDTTM=datetime IADMDTTM=dateTime));

run;

[Mercator]

It worked! Thanks.

[Kuldeep]

Could you please help me when i open up my sas following message comes up..

ERROR: Cannot open "c:\program files\SAS\SASFoundation\9.2\nls\en\sasv9.cfg"

SAS option '-PATH' not set.

Check configuration file, SAS environment options or command line options.

SAS option '-RESOURCESLOC' not set.

Check configuration file, SAS environment options or command line options.

FATAL ERROR: WRCODE=80000803, MODULE='HIINIT': PATH OPTION UNAVAILABLE

Traceback

SASTBTraceBackCtx has been called with a

string ("vacrash") instead of a CONTEXT pointer.

Address   Frame     (DBGHELP API Version 4.0 rev 5)

67EE5DB8  028EFCA4  0001:000D4DB8 sashost.dll

67EE5BD8  028EFCB0  0001:000D4BD8 sashost.dll

67E1D4CA  028EFCD0  0001:0000C4CA sashost.dll

67E11A3F  028EFD28  sashost:rtmdoit+0x1E3

67EADB30  028EFD38  0001:0009CB30 sashost.dll

67E3C5BF  028EFD90  0001:0002B5BF sashost.dll

67E3ADA0  028EFDC4  0001:00029DA0 sashost.dll

67E1119D  028EFED0  0001:0000019D sashost.dll

67E17432  0290FF34  0001:00006432 sashost.dll

67E261B5  0290FF5C  0001:000151B5 sashost.dll

67E2237F  0290FF74  0001:0001137F sashost.dll

67E26DE0  0290FF88  0001:00015DE0 sashost.dll

763C1154  0290FF94  kernel32:BaseThreadInitThunk+0x12

77B0B299  0290FFD4  ntdll:RtlInitializeExceptionChain+0x63

77B0B26C  0290FFEC  ntdll:RtlInitializeExceptionChain+0x36

i think something is wrong with config files if you have config files arguments please send me my SAS version is 9.0

[bhadrappa_molgi]

this doesn't work when using it from UNIX sas. log says

NOTE: Libref TEST12 was successfully assigned as follows:

Engine: XLSX

Physical Name: /xxxxx/yyyyy.xlsx

24

25

26

27 data sheet1;

28

29 set test12."Sheet1$"n(dbSASType=(HADMDTTM=datetime IADMDTTM=dateTime));

_________

22

ERROR 22-7: Invalid option name DBSASTYPE.

30

31 run;