I am importing two Excel csv files into a SAS program. The first import went fine with all the records importing properly. The files are of the same number of records - they are two views of tracer records from the MS Arias software program. The second import has errors. This is the proc import code.
Proc import datafile = "I:\Health Analytics\AMB_INFORMATICS\Arias_All_Contacts_Excel_Upload\System_All Contacts 9-7-2020 9-05-22 AM.csv"
out = ARIAS.System_Contacts_per_tracer
dbms = csv
replace;
getnames=yes;
run;
And the log
NOTE: 74467 records were read from the infile 'I:\Health Analytics\AMB_INFORMATICS\Arias_All_Contacts_Excel_Upload\System_All Contacts 9-7-2020 9-05-22 AM.csv'. The minimum record length was 1. The maximum record length was 375. NOTE: The data set ARIAS.SYSTEM_CONTACTS_PER_TRACER has 74467 observations and 20 variables. NOTE: Compressing data set ARIAS.SYSTEM_CONTACTS_PER_TRACER decreased size by 48.25 percent. Compressed is 222 pages; un-compressed would require 429 pages. NOTE: DATA statement used (Total process time): real time 0.29 seconds cpu time 0.29 seconds
Errors detected in submitted DATA step. Examine log.
The problem is there are only 41K obs in the file. This file is the same size of the first file the same which I imported. So I don't know where the 74,467 came from. When I examine the csv file after exporting to Excel from Arias, this file has the same records as the first (successful) csv that I imported (a little over 41K).
Further examining the log I see this error
NOTE: The infile 'I:\Health Analytics\AMB_INFORMATICS\Arias_All_Contacts_Excel_Upload\System_All
Contacts 9-7-2020 9-05-22 AM.csv' is:
Filename=I:\Health Analytics\AMB_INFORMATICS\Arias_All_Contacts_Excel_Upload\System_All
Contacts 9-7-2020 9-05-22 AM.csv,
RECFM=V,LRECL=32767,File Size (bytes)=13618067,
Last Modified=07Sep2020:09:07:35,
Create Time=07Sep2020:09:07:35
NOTE: Invalid data for DOB in line 32 49-75.
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9--
32 Beaverton, OR 97008",Beaverton,Washington,Phone,sarkodiefrancisca@gmail.com,971-706-1617,8/1
93 9/2020,9/2/2020,Washington CHD,Jose Ramirez,8/21/2020 9:30, 151
_Do_Not_Modify__Contact=Beaverton _Do_Not_Modify__Row_Checksum=OR 97008" _Do_Not_Modify__Modified_On=.
Last_Name=Washington First_Name=Phone DOB=. Monitoring_Status=971-706-1617 Status=8/19/202
Address_1=9/2/2020 City=Washingto County=Jose Ramir Preferred_Method_of_Contact=8/21/ Email=
Mobile_Phone= Date_of_Last_Exposure=. End_of_Quarantine_Period=. Jurisdiction= Owner= Created_On=.
Language= _ERROR_=1 _N_=31
NOTE: Invalid data for DOB in line 458 51-70.
I just entered the first line. It reads 'problem with dob in line 32...'
Checking the csv file doesn't show any miscoded values for dob at line 32. I have attached a small sample of the csv file. The dob field is sparsely populated. The shaded record is line 32 mentioned in the SAS log and the dob field is empty.
Finally, I tried to read the data using the infile statement
Data ARIAS.System_contacts_per_tracer;
Infile 'I:\Health Analytics\AMB_INFORMATICS\ARIAS_All_Contacts_Excel_Upload\System_All Contacts 9-7-2020 9-05-22 AM.csv' dsd truncover firstobs=2;
Input
_Do_Not_Modify__Contact: $36.
_Do_Not_Modify__Row_Checksum: $88.
_Do_Not_Modify__Modified_On: anydtdtm40.
Last_Name:$12.
First_Name:$24.
DOB: mmddyy10.
Monitoring_Status:$30.
Status: $8.
Address_1:$17.
City:$9.
County:$10.
Preferred_Method_of_Contact:$5.
Email: $24.
Mobile_Phone: $14.
Date_of_Last_Exposure: mmddyy10.
End_of_Quarantine_Period: mmddyy10.
Jurisdiction:$26.
Owner:$16.
Created_On: anydtdtm40.
Language:$11.
;
Run;
But I received an error
ERROR: File is in use, I:\Health Analytics\AMB_INFORMATICS\ARIAS_All_Contacts_Excel_Upload\System_All
Contacts 9-7-2020 9-05-22 AM.csv.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set ARIAS.SYSTEM_CONTACTS_PER_TRACER may be incomplete. When this step was stopped
there were 0 observations and 20 variables.
WARNING: Data set ARIAS.SYSTEM_CONTACTS_PER_TRACER was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.05 seconds
cpu time 0.01 seconds
So that is the error that I am struggling with. This data is imported every morning from Opera software so I thought maybe there was a glitch at that point. But the first view imported without errors into SAS. So I am not sure.
Any help, suggestions, or directions to next steps would be appreciated.
wlierman
... View more