topic Import EXCEL or Txt file with datetime value in SAS Procedures

Import EXCEL or Txt file with datetime value

Addison — Tue, 28 Apr 2015 21:12:16 GMT

I have some data abstracting from EHR system and all variables in this dataset are visit_id, pat_id, DEPT_KEY, PAYOR_KEY,CONTACT_DT_KEY,DICT_ENC_TYPE_KEY, APPT_CHECKOUT_DT, APPT_CHECKIN_DT,HOSP_ADMIT_DT,HOSP_DISCHRG_DT,do

Here are some example

1520,3811,5035,2311,20130327,83,3/29/2013 21:38:38,3/29/2013 19:14:56,3/29/2013 19:13:58,3/30/2013 10:20:58,6/3/1997 10:21:58

3094,3812,0,2387,20130322,83,,,4/1/2013 10:44:29,4/1/2013 15:00:29,2/4/1997 20:16:29

2173,3813,0,2611,20130313,83,,,3/20/2013 13:51:06,3/21/2013 14:31:06,2/12/1997 0:29:06

2559,3814,4825,2358,20130317,108,3/23/2013 8:15:14,3/23/2013 6:54:57,,,1/10/2001 20:28:46

1870,3815,5417,2368,20130322,83,3/25/2013 9:46:58,3/25/2013 12:21:20,3/25/2013 12:20:45,3/25/2013 20:41:45,1/3/1997 20:42:45

886,3816,0,2366,20130301,83,,,3/13/2013 10:55:22,3/13/2013 18:08:22,2/4/1997 23:28:22

I try to import these data by using EXCEL and text file, but SAS keeps showing Error message in the log window.

Here is the coding that I wrote

LIBNAME myxlsx XLSX '\\client\c\Pei-Lin\chop\\visit\VISIT.xlsx';

data VISIT;

set myxlsx."VISIT$"n(dbSASType=(APPT_CHECKOUT_DT=datetime APPT_CHECKIN_DT=dateTime HOSP_ADMIT_DT=dateTime HOSP_DISCHRG_DT=dateTime dob=dateTime));

run;

685 LIBNAME xlsx '\\client\c\Pei-Lin\chop\\visit\VISIT.xlsx';

ERROR: Connect: Class not registered

ERROR: Error in the LIBNAME statement.

686 data VISIT;

687 set xlsx."VISIT$"n(dbSASType=(APPT_CHECKOUT_DT=datetime APPT_CHECKIN_DT=dateTime

687! HOSP_ADMIT_DT=dateTime HOSP_DISCHRG_DT=dateTime dob=dateTime));

ERROR: Libref XLSX is not assigned.

688 run;

And then I try to import those data using text file

DATA visit;

INFILE '\\client\C\Pei-Lin\chop\data\visit.txt' dlm="," dsd truncover;

Input

visit_id

pat_id

DEPT_KEY

PAYOR_KEY

CONTACT_DT_KEY

DICT_ENC_TYPE_KEY

APPT_CHECKOUT_DT ymddttm20.2

APPT_CHECKIN_DT ymddttm20.2

HOSP_ADMIT_DT ymddttm20.2

HOSP_DISCHRG_DT ymddttm20.2

dob ymddttm20.2;

format

visit_id 4.

pat_id 4.

DEPT_KEY 5.

PAYOR_KEY 4.

CONTACT_DT_KEY 8.

DICT_ENC_TYPE_KEY 3.

APPT_CHECKOUT_DT datetime20.

APPT_CHECKIN_DT datetime20.

HOSP_ADMIT_DT datetime20.

HOSP_DISCHRG_DT datetime20.

dob datetime20.;

run;

I still got many ERROR messages and SAS didn't read the raw data correctly.

NOTE: The infile '\\client\C\Pei-Lin\chop\data\visit.txt' is:

(no system-specific pathname available),

(no system-specific file attributes available)

NOTE: Invalid data for APPT_CHECKOUT_DT in line 1 33-52.

NOTE: Invalid data for APPT_CHECKIN_DT in line 1 53-72.

NOTE: Invalid data for HOSP_ADMIT_DT in line 1 73-92.

NOTE: Invalid data for HOSP_DISCHRG_DT in line 1 93-112.

NOTE: Invalid data for dob in line 1 113-125.

RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9

1 1520,3811,5035,2311,20130327,83,3/29/2013 21:38:38,3/29/2013 19:14:56,3/29/2013 19:13:58,3

91 /30/2013 10:20:58,6/3/1997 10:21:58 125

visit_id=1520 pat_id=3811 DEPT_KEY=5035 PAYOR_KEY=2311 CONTACT_DT_KEY=20130327 DICT_ENC_TYPE_KEY=83

APPT_CHECKOUT_DT=. APPT_CHECKIN_DT=. HOSP_ADMIT_DT=. HOSP_DISCHRG_DT=. dob=. _ERROR_=1 _N_=1

NOTE: Invalid data for APPT_CHECKOUT_DT in line 2 30-49.

Can anyone tell me which step I did wrong? Thank you so muche

NOTE: Invalid data for APPT_CHECKIN_DT in line 2 50-69.

NOTE: Invalid data for HOSP_ADMIT_DT in line 2 70-84.

Re: Import EXCEL or Txt file with datetime value

art297 — Tue, 28 Apr 2015 21:47:19 GMT

Can't say way the libname method didn't work. Do you license sas/access for pc products?

As for the data step to read the text file, the following worked for me:

DATA visit;

/*INFILE '\\client\C\Pei-Lin\chop\data\visit.txt' dlm="," dsd truncover;*/

infile cards dlm="," dsd truncover;

informat APPT_CHECKOUT_DT

APPT_CHECKIN_DT

HOSP_ADMIT_DT

HOSP_DISCHRG_DT

dob anydtdtm20.;

Input

visit_id

pat_id

DEPT_KEY

PAYOR_KEY

CONTACT_DT_KEY

DICT_ENC_TYPE_KEY

APPT_CHECKOUT_DT

APPT_CHECKIN_DT

HOSP_ADMIT_DT

HOSP_DISCHRG_DT

dob;

format

visit_id 4.

pat_id 4.

DEPT_KEY 5.

PAYOR_KEY 4.

CONTACT_DT_KEY 8.

DICT_ENC_TYPE_KEY 3.

APPT_CHECKOUT_DT

APPT_CHECKIN_DT

HOSP_ADMIT_DT

HOSP_DISCHRG_DT

dob;

cards;

1520,3811,5035,2311,20130327,83,3/29/2013 21:38:38,3/29/2013 19:14:56,3/29/2013 19:13:58,3/30/2013 10:20:58,6/3/1997 10:21:58

3094,3812,0,2387,20130322,83,,,4/1/2013 10:44:29,4/1/2013 15:00:29,2/4/1997 20:16:29

2173,3813,0,2611,20130313,83,,,3/20/2013 13:51:06,3/21/2013 14:31:06,2/12/1997 0:29:06

2559,3814,4825,2358,20130317,108,3/23/2013 8:15:14,3/23/2013 6:54:57,,,1/10/2001 20:28:46

1870,3815,5417,2368,20130322,83,3/25/2013 9:46:58,3/25/2013 12:21:20,3/25/2013 12:20:45,3/25/2013 20:41:45,1/3/1997 20:42:45

886,3816,0,2366,20130301,83,,,3/13/2013 10:55:22,3/13/2013 18:08:22,2/4/1997 23:28:22

;

Re: Import EXCEL or Txt file with datetime value

BharathBandi — Tue, 28 Apr 2015 22:20:28 GMT

Take a look at this :

43933 - "Connection Failed" and "Connect: Class not registered" errors occur when using the Import Wizard with SAS® 9.3 on Windows X64 systems

37412 - Errors occur when you import Microsoft Excel or Microsoft Access files into SAS® 9.2

Simply searching for the text error in search engine gives you interesting results.

Re: Import EXCEL or Txt file with datetime value

Kurt_Bremser — Wed, 29 Apr 2015 06:33:21 GMT

For the first problem, you most probably have not licensed SAS/ACCESS to PC Files.

If you are on SAS 9.4, you could try to save the Excel data as .xlsx and use libname xlsx.

I woud read the datetime fields as strings, split them around the blank with the split function, then input each part with the proper informat, and then create the datetime value by using

format dtimeval datetime22.3; dtimeval = dateval * 86400 + timeval;

Re: Import EXCEL or Txt file with datetime value

Addison — Wed, 29 Apr 2015 14:19:28 GMT

Thank you ALL! I think my school did not buy license for SAS/ACCESS to PC files therefore I couldn't use libname xlsx.