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.
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
;
Take a look at this :
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.
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;
Thank you ALL! I think my school did not buy license for SAS/ACCESS to PC files therefore I couldn't use libname xlsx.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.