Help using Base SAS procedures

Import EXCEL or Txt file with datetime value

Reply
New Contributor
Posts: 4

Import EXCEL or Txt file with datetime value

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

b

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.

PROC Star
Posts: 7,364

Re: Import EXCEL or Txt file with datetime value

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

;

Contributor
Posts: 34

Re: Import EXCEL or Txt file with datetime value

Super User
Posts: 6,966

Re: Import EXCEL or Txt file with datetime value

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor
Posts: 4

Re: Import EXCEL or Txt file with datetime value

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

Ask a Question
Discussion stats
  • 4 replies
  • 437 views
  • 0 likes
  • 4 in conversation