Hi there,
I'm having some difficulty importing a CSV file into SAS University Edition. Here is my code:
proc import datafile="&path/qualitylife01.csv" out=qualitylife01 dbms=csv replace; getnames=yes; run;
When I run this code, I get the following error:
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 55 56 proc import datafile="&path/qualitylife01.csv" 57 out=qualitylife01 58 dbms=csv 59 replace; 60 getnames=yes; 61 run; NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to WORK.PARMS.PARMS.SLIST. Unable to sample external file, no data in first 5 records. ERROR: Import unsuccessful. See SAS Log for details. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.30 seconds cpu time 0.21 seconds 62 63 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 75
I've also made various efforts to import the data using a data step, and I get even more garbled responses in the log:
data work.lifequality2; infile "&path/qualitylife01.csv" dlm=','; input Subject Age Sex $ EQ5D_1 ED5D_2 EQ5D_3 EQ5D_4 EQ5D_5 VAS q1 q2 q3 q4 q5 q6 q7 q8 q9 q10 q11 q12 q13 q14 q15 q16 q17; run;
Here's the log:
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 55 56 data work.qualitylife01; 57 infile "&path/qualitylife01.csv" dlm=','; 58 input Subject Age Sex $ EQ5D_1 ED5D_2 EQ5D_3 EQ5D_4 EQ5D_5 59 VAS q1 q2 q3 q4 q5 q6 q7 q8 q9 q10 q11 q12 q13 q14 q15 q16 q17; 60 run; NOTE: The infile "/folders/myfolders/SPPH 541/qualitylife01.csv" is: Filename=/folders/myfolders/SPPH 541/qualitylife01.csv, Owner Name=root,Group Name=vboxsf, Access Permission=-rwxrwx---, Last Modified=27Jan2016:00:20:18, File Size (bytes)=1873 NOTE: Invalid data for Subject in line 1 1-7. NOTE: Invalid data for Age in line 1 9-11. NOTE: Invalid data for EQ5D_1 in line 1 17-23. NOTE: Invalid data for ED5D_2 in line 1 25-31. NOTE: Invalid data for EQ5D_3 in line 1 33-39. NOTE: Invalid data for EQ5D_4 in line 1 41-47. NOTE: Invalid data for EQ5D_5 in line 1 49-55. etc. I then get the ruler printed out in the log with the printout of my unread CSV file showing up.
Obviously, something about my code isn't working right. Would really appreciate any suggestions anyone may have as to how to edit or amend the code to make the import successful.
Thanks very much!
How to read one of these:
The rule line is a column indicator, the + means a column ending in 5 such as 5, 15, 25, the numbers indicate 10, 20 , 30 etc.
The lines that start with 101, 201 and 301 are showing that line starting at the 101st character in the line. The two rows Zone and NUMR are the ASCII hex codes for the character above them: 31 => 1, 32 => 2 etc. The 2C is the comma character.
The 1 that appears under the word RULE indicates which line in the file was read. The data shown looks like a header row.
Your code
56 data work.qualitylife01;
57 infile "&path/qualitylife01.csv" dlm=',';
58 input Subject Age Sex $ EQ5D_1 ED5D_2 EQ5D_3 EQ5D_4 EQ5D_5
59 VAS q1 q2 q3 q4 q5 q6 q7 q8 q9 q10 q11 q12 q13 q14 q15 q16 q17;
60 run;
says to start reading on the first line of the file. If you add FirstObs=2 on the INFILE statement it will not read the header row.
However it also appears as if the data is not treated as ending where you expect. The value q17.1 seems to be where the data should start with subject 1, a 32 year old female. the "." and 0D looks like the file uses a Apple based Carriage Return character and believe that the university edition is expecting a CarriageReturn LineFeed pair to mark the end of line. If you add TERMSTR=CR to the infile statement I think that will correct the issue.
Can you post the CSV file? As Invalida data is found in the first column, I suspect you might have something wrong with the file - maybe a header row, or special characters, but I can't tell without seeing it.
Hi everyone,
Thanks for the quick and kind responses. I've not had a chance to try out any of the solutions yet, but wondered if the problem was in the CSV file, given that it was converted from XLS. I did look at it in TextWrangler, but couldn't see anything obviously wrong.
Thanks for your help!
Problem is here :
File don't have data in the first five rows.
56 proc import datafile="&path/qualitylife01.csv" 57 out=qualitylife01 58 dbms=csv 59 replace; 60 getnames=yes; 61 run; NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to WORK.PARMS.PARMS.SLIST. Unable to sample external file, no data in first 5 records. ERROR: Import unsuccessful. See SAS Log for details. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.30 seconds cpu time 0.21 seconds
Try datarow= option:
proc import datafile="&path/qualitylife01.csv" 57 out=qualitylife01 58 dbms=csv 59 replace; 60 getnames=yes;
datarow=100; 61 run;
Thanks - I had tried this previously with datarow=2, but I get this error:
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 55 56 proc import datafile="&path/qualitylife01.csv" 57 out=qualitylife01 58 dbms=csv 59 replace; 60 getnames=yes; 61 datarow=100; 62 run; NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to WORK.PARMS.PARMS.SLIST. Unable to sample external file, no data in first 5 records. ERROR: Import unsuccessful. See SAS Log for details.
Very interesting . Maybe someone can give some good advice or SAS enhance proc import in near future for such kind question.
filename xx temp;
data _null_;
file xx;
infile "/folders/myfolders/jd.csv" lrecl=32767;
input;
if not missing(_infile_) then put _infile_;
run;
proc import datafile=xx
out=qualitylife01
dbms=csv
replace;
run;
The ruler and data that you didn't show probably is a good diagnostic of the problem but you just don't know how to read it yet.
56 data work.qualitylife01;
57 infile "&path/qualitylife01.csv" dlm=',';
58 input Subject Age Sex $ EQ5D_1 ED5D_2 EQ5D_3 EQ5D_4 EQ5D_5
59 VAS q1 q2 q3 q4 q5 q6 q7 q8 q9 q10 q11 q12 q13 q14 q15 q16 q17;
60 run;
NOTE: The infile "/folders/myfolders/SPPH 541/qualitylife01.csv" is:
Filename=/folders/myfolders/SPPH 541/qualitylife01.csv,
Owner Name=root,Group Name=vboxsf,
Access Permission=-rwxrwx---,
Last Modified=27Jan2016:00:20:18,
File Size (bytes)=1873
NOTE: Invalid data for Subject in line 1 1-7.
NOTE: Invalid data for Age in line 1 9-11.
NOTE: Invalid data for EQ5D_1 in line 1 17-23.
NOTE: Invalid data for ED5D_2 in line 1 25-31.
NOTE: Invalid data for EQ5D_3 in line 1 33-39.
NOTE: Invalid data for EQ5D_4 in line 1 41-47.
NOTE: Invalid data for EQ5D_5 in line 1 49-55.
In your code and notes above, since you do not declare the types of any variables with either an INFORMAT or Length, Subject, Age and the ED and EQ variables are assumed to be numeric. So If the value read has anything other than a number or blank in the columns read you will get an invalid data.
Show the ruler and notes and we can see what happened.
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0 1 Subject,Age,Sex,EQ5D_Q1,EQ5D_Q2,EQ5D_Q3,EQ5D_Q4,EQ5D_Q5,VAS,q1,q2,q3,q4,q5,q6,q7,q8,q9,q10,q11,q12,q 101 13,q14,q15,q16,q17.1,32,F,1,1,1,1,1,100,2,2,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1.2,59,F,1,1,2,1,2,75,3,3,1, ZONE 3327332733273327330323324232323232323332323232323232323232323232323232323032332423232323232332323232 NUMR 13C114C115C116C117D1C32C6C1C1C1C1C1C100C2C2C1C1C1C1C1C1C1C1C1C1C1C1C1C1C1D2C59C6C1C1C2C1C2C75C3C3C1C 201 1,1,1,1,2,1,1,2,2,1,2,1,2,1.3,37,F,1,1,1,1,2,96,2,2,1,1,1,1,2,2,1,1,2,1,1,2,2,1,1.4,42,F,1,1,1,1,1,8 ZONE 3232323232323232323232323230323324232323232323323232323232323232323232323232323230323324232323232323 NUMR 1C1C1C1C2C1C1C2C2C1C2C1C2C1D3C37C6C1C1C1C1C2C96C2C2C1C1C1C1C2C2C1C1C2C1C1C2C2C1C1D4C42C6C1C1C1C1C1C8 301 5,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,1.5,35,F,1,1,1,2,1,60,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,1,1.6,41,F,1,1 ZONE 3232323232323232323232323232323232303233242323232323233232323232323232323232323232323232303233242323 NUMR 5C1C1C1C1C1C1C1C1C1C1C1C1C1C1C1C2C1D5C35C6C1C1C1C2C1C60C1C1C1C1C1C1C1C1C1C1C1C1C1C1C2C1C1D6C41C6C1C1
As requested. You're right - I have limited ideas as to what's going on here. I see my data, but it's clearly not making it into the system correctly.
Can you post the Excel file, we can try exporting and reading it in. Also, as always, have you tried writing a datastep import? Proc import is just a guessing system which generates a datastep import, so if its guesses are wrong...
Yes, I have tried using a data step - tried both approaches in the hopes something would catch. Data step code is included in this thread.
How to read one of these:
The rule line is a column indicator, the + means a column ending in 5 such as 5, 15, 25, the numbers indicate 10, 20 , 30 etc.
The lines that start with 101, 201 and 301 are showing that line starting at the 101st character in the line. The two rows Zone and NUMR are the ASCII hex codes for the character above them: 31 => 1, 32 => 2 etc. The 2C is the comma character.
The 1 that appears under the word RULE indicates which line in the file was read. The data shown looks like a header row.
Your code
56 data work.qualitylife01;
57 infile "&path/qualitylife01.csv" dlm=',';
58 input Subject Age Sex $ EQ5D_1 ED5D_2 EQ5D_3 EQ5D_4 EQ5D_5
59 VAS q1 q2 q3 q4 q5 q6 q7 q8 q9 q10 q11 q12 q13 q14 q15 q16 q17;
60 run;
says to start reading on the first line of the file. If you add FirstObs=2 on the INFILE statement it will not read the header row.
However it also appears as if the data is not treated as ending where you expect. The value q17.1 seems to be where the data should start with subject 1, a 32 year old female. the "." and 0D looks like the file uses a Apple based Carriage Return character and believe that the university edition is expecting a CarriageReturn LineFeed pair to mark the end of line. If you add TERMSTR=CR to the infile statement I think that will correct the issue.
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 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: