topic Re: Importing date-time variables from Excel into SAS in Microsoft Integration with SAS

Importing date-time variables from Excel into SAS

Mercator — Thu, 07 Mar 2013 14:52:36 GMT

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

Re: Importing date-time variables from Excel into SAS

mlamias — Thu, 07 Mar 2013 15:22:19 GMT

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?

Re: Importing date-time variables from Excel into SAS

Mercator — Thu, 07 Mar 2013 15:38:01 GMT

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

Re: Importing date-time variables from Excel into SAS

mlamias — Thu, 07 Mar 2013 16:00:46 GMT

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;

Re: Importing date-time variables from Excel into SAS

Mercator — Thu, 07 Mar 2013 16:22:36 GMT

It worked! Thanks.

Re: Importing date-time variables from Excel into SAS

Kuldeep — Sat, 09 Mar 2013 20:15:26 GMT

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

Re: Importing date-time variables from Excel into SAS

bhadrappa_molgi — Wed, 21 Oct 2015 15:54:14 GMT

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

27 data sheet1;

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

_________

ERROR 22-7: Invalid option name DBSASTYPE.

31 run;