unsuccessful import data from excel to sas using import data function

Agha — Mon, 11 Mar 2024 21:22:42 GMT

This encoding will be used to process the file.
1 /**********************************************************************
2 * PRODUCT: SAS
3 * VERSION: 9.4
4 * CREATOR: External File Interface
5 * DATE: 11MAR24
6 * DESC: Generated SAS Datastep Code
7 * TEMPLATE SOURCE: (None Specified.)
8 ***********************************************************************/
9 data WORK.lab ;
10 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
11 infile
13 informat encounter_study_id $6. ;
14 informat specimen_collected_timestamp anydtdtm40. ;
15 informat lab_base_name $5. ;
16 informat lab_component_name $44. ;
17 informat numeric_value best32. ;
18 informat string_value best32. ;
19 informat units $15. ;
20 format patient_study_id $6. ;
21 format encounter_study_id $6. ;
22 format specimen_collected_timestamp datetime. ;
23 format lab_base_name $5. ;
24 format lab_component_name $44. ;
25 format numeric_value best12. ;
26 format string_value best12. ;
27 format units $15. ;
28 input
29 patient_study_id $
30 encounter_study_id $
31 specimen_collected_timestamp
32 lab_base_name $
33 lab_component_name $
34 numeric_value
35 string_value
36 units $
37 ;
38 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
39 run;

NOTE: A byte-order mark in the file

NOTE: Invalid data for string_value in line 69 87-90.
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9--
69 GJYXY2,GN3PV2,2012-10-05 10:29:00,UMA,"MICROALBUMIN, URINE OLYMPUS (UWMF)",10000000.0,<5.0,m
93 g/L 95
patient_study_id=GJYXY2 encounter_study_id=GN3PV2 specimen_collected_timestamp=05OCT12:10:29:00
lab_base_name=UMA lab_component_name=MICROALBUMIN, URINE OLYMPUS (UWMF) numeric_value=10000000
string_value=. units=mg/L _ERROR_=1 _N_=68
NOTE: Invalid data for string_value in line 188 87-90.
188 GJYWZS,GL6NQU,2013-05-06 09:25:00,UMA,"MICROALBUMIN, URINE OLYMPUS (UWMF)",10000000.0,<5.0,m
93 g/L 95
patient_study_id=GJYWZS encounter_study_id=GL6NQU specimen_collected_timestamp=06MAY13:09:25:00
lab_base_name=UMA lab_component_name=MICROALBUMIN, URINE OLYMPUS (UWMF) numeric_value=10000000
string_value=. units=mg/L _ERROR_=1 _N_=187
NOTE: Invalid data for string_value in line 201 80-98.
201 GJZCP1,GL9ILP,2013-04-19 12:58:00,UMACR,ALB/CRET RATIO URINE (UWHC),10000000.0,Calculation I
93 nvalid,mg/g cret 108
patient_study_id=GJZCP1 encounter_study_id=GL9ILP specimen_collected_timestamp=19APR13:12:58:00
lab_base_name=UMACR lab_component_name=ALB/CRET RATIO URINE (UWHC) numeric_value=10000000
string_value=. units=mg/g cret _ERROR_=1 _N_=200
NOTE: Invalid data for string_value in line 202 80-99.
202 GJZFJD,GLYUUA,2014-12-05 11:08:00,UMACR,ALB/CRET RATIO URINE (UWHC),10000000.0,Unable to cal
93 culate.,mg/g cret 109
patient_study_id=GJZFJD encounter_study_id=GLYUUA specimen_collected_timestamp=05DEC14:11:08:00
lab_base_name=UMACR lab_component_name=ALB/CRET RATIO URINE (UWHC) numeric_value=10000000
string_value=. units=mg/g cret _ERROR_=1 _N_=201
NOTE: Invalid data for string_value in line 480 79-82.
480 GJZ4NH,GKG3FF,2017-06-13 08:44:00,UMA,"MICROALBUMIN, URINE (UWMF)",10000000.0,<5.0,mg/L 87
patient_study_id=GJZ4NH encounter_study_id=GKG3FF specimen_collected_timestamp=13JUN17:08:44:00
lab_base_name=UMA lab_component_name=MICROALBUMIN, URINE (UWMF) numeric_value=10000000 string_value=.
units=mg/L _ERROR_=1 _N_=479
NOTE: Invalid data for string_value in line 481 78-79.
481 GJZ8XU,GJPLFT,2020-06-15 10:20:00,UMA,"MICROALBUMIN, URINE (UWH)",10000000.0,<5,mg/L 84
patient_study_id=GJZ8XU encounter_study_id=GJPLFT specimen_collected_timestamp=15JUN20:10:20:00
lab_base_name=UMA lab_component_name=MICROALBUMIN, URINE (UWH) numeric_value=10000000 string_value=.
units=mg/L _ERROR_=1 _N_=480
NOTE: Invalid data for string_value in line 500 87-90.
500 GJZ95J,GMMH3E,2012-07-26 12:16:00,UMA,"MICROALBUMIN, URINE OLYMPUS (UWMF)",10000000.0,<5.0,m
93 g/L 95
patient_study_id=GJZ95J encounter_study_id=GMMH3E specimen_collected_timestamp=26JUL12:12:16:00
lab_base_name=UMA lab_component_name=MICROALBUMIN, URINE OLYMPUS (UWMF) numeric_value=10000000
string_value=. units=mg/L _ERROR_=1 _N_=499
NOTE: Invalid data for string_value in line 547 78-79.
547 GJZ5BE,GL81B5,2021-12-14 10:23:00,UMA,"MICROALBUMIN, URINE (UWH)",10000000.0,<5,mg/L 84
patient_study_id=GJZ5BE encounter_study_id=GL81B5 specimen_collected_timestamp=14DEC21:10:23:00
lab_base_name=UMA lab_component_name=MICROALBUMIN, URINE (UWH) numeric_value=10000000 string_value=.
units=mg/L _ERROR_=1 _N_=546
NOTE: Invalid data for string_value in line 614 87-90.
614 GJZ5VF,GJIGU7,2011-01-03 08:16:00,UMA,"MICROALBUMIN, URINE OLYMPUS (UWMF)",10000000.0,<5.0,m
93 g/L 95
patient_study_id=GJZ5VF encounter_study_id=GJIGU7 specimen_collected_timestamp=03JAN11:08:16:00
lab_base_name=UMA lab_component_name=MICROALBUMIN, URINE OLYMPUS (UWMF) numeric_value=10000000
string_value=. units=mg/L _ERROR_=1 _N_=613
NOTE: Invalid data for string_value in line 833 76-77.
833 GJZ0N5,GJIWHW,2015-02-17 09:11:00,UMA,MICROALBUMIN URINE (UWHC),10000000.0,<5,mg/L 82
patient_study_id=GJZ0N5 encounter_study_id=GJIWHW specimen_collected_timestamp=17FEB15:09:11:00
lab_base_name=UMA lab_component_name=MICROALBUMIN URINE (UWHC) numeric_value=10000000 string_value=.
units=mg/L _ERROR_=1 _N_=832
NOTE: Invalid data for string_value in line 854 78-79.
854 GJZE5W,GNB0CL,2023-07-17 08:25:00,UMA,"MICROALBUMIN, URINE (UWH)",10000000.0,<5,mg/L 84
patient_study_id=GJZE5W encounter_study_id=GNB0CL specimen_collected_timestamp=17JUL23:08:25:00
lab_base_name=UMA lab_component_name=MICROALBUMIN, URINE (UWH) numeric_value=10000000 string_value=.
units=mg/L _ERROR_=1 _N_=853
NOTE: Invalid data for string_value in line 863 80-98.
863 GJYXGO,GM9EHO,2014-02-18 14:31:00,UMACR,ALB/CRET RATIO URINE (UWHC),10000000.0,Calculation I
93 nvalid,mg/g cret 108
patient_study_id=GJYXGO encounter_study_id=GM9EHO specimen_collected_timestamp=18FEB14:14:31:00
lab_base_name=UMACR lab_component_name=ALB/CRET RATIO URINE (UWHC) numeric_value=10000000
string_value=. units=mg/g cret _ERROR_=1 _N_=862
NOTE: Invalid data for string_value in line 890 78-79.
890 GJZ9HH,GLCQ2I,2022-02-10 16:13:00,UMA,"MICROALBUMIN, URINE (UWH)",10000000.0,<5,mg/L 84
patient_study_id=GJZ9HH encounter_study_id=GLCQ2I specimen_collected_timestamp=10FEB22:16:13:00
lab_base_name=UMA lab_component_name=MICROALBUMIN, URINE (UWH) numeric_value=10000000 string_value=.
units=mg/L _ERROR_=1 _N_=889
NOTE: Invalid data for string_value in line 1083 76-85.
1083 GJZENA,GKXVLB,2014-11-09 12:30:00,LDL,LDL CHOLESTEROL (OUTSIDE),10000000.0,Not Tested,mg/dL 91
patient_study_id=GJZENA encounter_study_id=GKXVLB specimen_collected_timestamp=09NOV14:12:30:00
lab_base_name=LDL lab_component_name=LDL CHOLESTEROL (OUTSIDE) numeric_value=10000000 string_value=.
units=mg/dL _ERROR_=1 _N_=1082
NOTE: Invalid data for string_value in line 1103 87-90.
1103 GJZ8Q1,GLUYEK,2015-06-28 11:22:00,UMA,"MICROALBUMIN, URINE OLYMPUS (UWMF)",10000000.0,<5.0,m
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9--
93 g/L 95
patient_study_id=GJZ8Q1 encounter_study_id=GLUYEK specimen_collected_timestamp=28JUN15:11:22:00
lab_base_name=UMA lab_component_name=MICROALBUMIN, URINE OLYMPUS (UWMF) numeric_value=10000000
string_value=. units=mg/L _ERROR_=1 _N_=1102
NOTE: Invalid data for string_value in line 1354 87-90.
1354 GJYY34,GLKCPR,2015-06-04 08:14:00,UMA,"MICROALBUMIN, URINE OLYMPUS (UWMF)",10000000.0,<5.0,m
93 g/L 95
patient_study_id=GJYY34 encounter_study_id=GLKCPR specimen_collected_timestamp=04JUN15:08:14:00
lab_base_name=UMA lab_component_name=MICROALBUMIN, URINE OLYMPUS (UWMF) numeric_value=10000000
string_value=. units=mg/L _ERROR_=1 _N_=1353
NOTE: Invalid data for string_value in line 1386 78-79.
1386 GJYZ61,GLHF4L,2022-05-18 08:56:00,UMA,"MICROALBUMIN, URINE (UWH)",10000000.0,<5,mg/L 84
patient_study_id=GJYZ61 encounter_study_id=GLHF4L specimen_collected_timestamp=18MAY22:08:56:00
lab_base_name=UMA lab_component_name=MICROALBUMIN, URINE (UWH) numeric_value=10000000 string_value=.
units=mg/L _ERROR_=1 _N_=1385
NOTE: Invalid data for string_value in line 1529 87-90.
1529 GJZ1SW,GKHA62,2011-03-08 08:46:00,UMA,"MICROALBUMIN, URINE OLYMPUS (UWMF)",10000000.0,<5.0,m
93 g/L 95
patient_study_id=GJZ1SW encounter_study_id=GKHA62 specimen_collected_timestamp=08MAR11:08:46:00
lab_base_name=UMA lab_component_name=MICROALBUMIN, URINE OLYMPUS (UWMF) numeric_value=10000000
string_value=. units=mg/L _ERROR_=1 _N_=1528
NOTE: Invalid data for string_value in line 1650 87-90.
1650 GJZ57M,GLXKGE,2012-01-02 09:04:00,UMA,"MICROALBUMIN, URINE OLYMPUS (UWMF)",10000000.0,<5.0,m
93 g/L 95
patient_study_id=GJZ57M encounter_study_id=GLXKGE specimen_collected_timestamp=02JAN12:09:04:00
lab_base_name=UMA lab_component_name=MICROALBUMIN, URINE OLYMPUS (UWMF) numeric_value=10000000
string_value=. units=mg/L _ERROR_=1 _N_=1649
NOTE: Invalid data for string_value in line 1738 71-90.
WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed.
1738 GJZMIA,GJRRKF,2015-09-05 05:12:00,LDL,EST. LDL CHOL (UWHC),10000000.0,Unable to calculate.,m
93 g/dL 96
patient_study_id=GJZMIA encounter_study_id=GJRRKF specimen_collected_timestamp=05SEP15:05:12:00
lab_base_name=LDL lab_component_name=EST. LDL CHOL (UWHC) numeric_value=10000000 string_value=.
units=mg/dL _ERROR_=1 _N_=1737
NOTE: 2167731 records were read from the infile
'R:\WI_EMR_DM_vision_loss_V2\Channa_RITM1118036_2023_11_02\CHANNA_RITM1118036_FILE04_2023_11_02_
subbed_2023_11_02.csv'.
The minimum record length was 33.
The maximum record length was 179.
NOTE: The data set WORK.LAB has 2167731 observations and 8 variables.
NOTE: DATA statement used (Total process time):
real time 1:30.71
cpu time 3.67 seconds

Errors detected in submitted DATA step. Examine log.

ERROR: Import unsuccessful. See SAS Log for details.

How I can fix it. I see the error is "string value" and I should ask sas that read it as character, not numerical value

topic unsuccessful import data from excel to sas using import data function in Statistical Procedures

unsuccessful import data from excel to sas using import data function