Problems Importing CSV File into SAS University Edition

[SeanInVancouver]

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!

[RW9]

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.

[SeanInVancouver]

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!

[Ksharp]

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;

[SeanInVancouver]

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.

[Ksharp]

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;
 

[ballardw]

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.

[SeanInVancouver]

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.

[RW9]

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...

[SeanInVancouver]

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.

[ballardw · Accepted solution]

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.

[SeanInVancouver]

Thank you! Adding those two lines to the infile statement did the trick. There certainly are idiosyncracies to SAS University Edition and Mac computers - very much appreciate your assistance!