DATA Step, Macro, Functions and more

Importing issue in csv file

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Importing issue in csv file

I'm having issues importing a csv. I'm using the following code:

 

libname xplant "/folders/myfolders/Transplant";

 

proc import out = xplant.xplantnew
DATAFILE = "/folders/myfolders/Transplant/xplantnew.csv"
DBMS = csv REPLACE;
run;

 

It still produces a large set of output data but there are also many errors in the log. For example, at the beginning, it reports:

 

NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to
WORK.PARMS.PARMS.SLIST.
 
And at the end, it reports:
 
Errors detected in submitted DATA step. Examine log.
20021 rows created in XPLANT.XPLANTNEW from /folders/myfolders/Transplant/xplantnew.csv.
 
ERROR: Import unsuccessful. See SAS Log for details.
 
I have used this exact code with a an earlier verison of the csv but I've had more columns added. I'm not sure what the difference is and why it isn't' working properly anymore.

Accepted Solutions
Solution
‎11-05-2017 02:55 PM
Super User
Super User
Posts: 7,847

Re: Importing issue in csv file

[ Edited ]
Posted in reply to SaraDianaPasik

Here is the first error message.

 NOTE: Invalid data for Other_Diagnosis_Codes_5 in line 38 353-357.
 RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0                      
 38        64478,19950001744167.0,Fc/wYN4Vjz6mC0hgOMCNsgbFY05zOTF6KKbXll60qe+w,1967-04,27.0,M,01,2,MASPETH,NY,1
      101  1378,61, ,001458,New York Presbyterian Hospital - New York Weill Cornell Center,7.0,09,60.0,7002054.
      201  0,,00091528,00091528, ,,,,C, , , , , ,03, , , ,1995-01,1995-01,1995-01-09,MON,10.0,2,1995-01-25,WED,
      301  14.0,16.0,0,01,2,3,1,585,40391,25041,9952,5990,0414,E9331, , , , , , , , , , , , , , , , , , , ,9,9,
      401  9,9,9, , , , , , , , , , , , , , , , , , , , , , , , ,5569,1995-01,+,0001,15.0,5523,8877,9203,8875,3
      501  995,9917,9923,9921, , , , , , ,1995-01,1995-01,1995-01,1995-01,1995-01,1995-01,1995-01,1995-01, , , 
      601  , , , ,+,+,+,+,+,+,+,+, , , , , , , ,40.0,0.0,,,,,3958942.0,1760000.0,2198942.0,0.0,0.0,0.0,1995-01-
      701  25,1995-01-09,2009.0,99681,5523,2303.0,1.0,2010.0,99999.0,0.0,0.0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
      801  0,0,0.0,1,1,57127,88.92613351877625,72.0,3.0,58.0,3.0,1995-01-25,1,1700,8059883.170812558,0.0,332979
      901  4.7698810836,0.0,0.0,0.0 924

PROC IMPORT decided that Other_Diagnosis_Codes_5 should be a number,

format Other_Diagnosis_Codes_5 best12. ;

but it looks like the value starts with the letter E.

 

Add the GUESSINGROWS statement to your PROC IMPORT code so that you can tell it to look at more lines of data before making its guess as to what the variables are.

http://documentation.sas.com/?docsetId=proc&docsetTarget=p13kvtl8ezj13in17i6m99jypcwi.htm&docsetVers...

 

Better still would be to remove the PROC IMPORT step and just write your own data step to read the data.  Sounds like you have created the CSV file so you should know what the columns are without having to guess whether they are numbers or character strings.

 

View solution in original post


All Replies
PROC Star
Posts: 1,190

Re: Importing issue in csv file

Posted in reply to SaraDianaPasik

Post your full log if you want help with the error messages. 

 

In my opinion, the INFILE statement in the data step is a much better choice when you want to import csv filesSmiley Happy

New Contributor
Posts: 3

Re: Importing issue in csv file

 It's super long, so I wasn't sure if I should include the whole thing. But I frankly don't know which parts are important. So, here goes -- see attached.
 
Solution
‎11-05-2017 02:55 PM
Super User
Super User
Posts: 7,847

Re: Importing issue in csv file

[ Edited ]
Posted in reply to SaraDianaPasik

Here is the first error message.

 NOTE: Invalid data for Other_Diagnosis_Codes_5 in line 38 353-357.
 RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0                      
 38        64478,19950001744167.0,Fc/wYN4Vjz6mC0hgOMCNsgbFY05zOTF6KKbXll60qe+w,1967-04,27.0,M,01,2,MASPETH,NY,1
      101  1378,61, ,001458,New York Presbyterian Hospital - New York Weill Cornell Center,7.0,09,60.0,7002054.
      201  0,,00091528,00091528, ,,,,C, , , , , ,03, , , ,1995-01,1995-01,1995-01-09,MON,10.0,2,1995-01-25,WED,
      301  14.0,16.0,0,01,2,3,1,585,40391,25041,9952,5990,0414,E9331, , , , , , , , , , , , , , , , , , , ,9,9,
      401  9,9,9, , , , , , , , , , , , , , , , , , , , , , , , ,5569,1995-01,+,0001,15.0,5523,8877,9203,8875,3
      501  995,9917,9923,9921, , , , , , ,1995-01,1995-01,1995-01,1995-01,1995-01,1995-01,1995-01,1995-01, , , 
      601  , , , ,+,+,+,+,+,+,+,+, , , , , , , ,40.0,0.0,,,,,3958942.0,1760000.0,2198942.0,0.0,0.0,0.0,1995-01-
      701  25,1995-01-09,2009.0,99681,5523,2303.0,1.0,2010.0,99999.0,0.0,0.0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
      801  0,0,0.0,1,1,57127,88.92613351877625,72.0,3.0,58.0,3.0,1995-01-25,1,1700,8059883.170812558,0.0,332979
      901  4.7698810836,0.0,0.0,0.0 924

PROC IMPORT decided that Other_Diagnosis_Codes_5 should be a number,

format Other_Diagnosis_Codes_5 best12. ;

but it looks like the value starts with the letter E.

 

Add the GUESSINGROWS statement to your PROC IMPORT code so that you can tell it to look at more lines of data before making its guess as to what the variables are.

http://documentation.sas.com/?docsetId=proc&docsetTarget=p13kvtl8ezj13in17i6m99jypcwi.htm&docsetVers...

 

Better still would be to remove the PROC IMPORT step and just write your own data step to read the data.  Sounds like you have created the CSV file so you should know what the columns are without having to guess whether they are numbers or character strings.

 

New Contributor
Posts: 3

Re: Importing issue in csv file

This worked, thanks a million!

Super User
Super User
Posts: 7,847

Re: Importing issue in csv file

Posted in reply to SaraDianaPasik

You need to post the actual error. 

 

The first message you posted is just caused by a bug in PROC IMPORT.  It expects to be able to write to the SASUSER library, which is stupid since there exists a SAS option (RSASUSER) that is specifically designed to prevent writing to the SASUSER library.  This note does not mean that PROC IMPORT had any real trouble.

 

The second message is generated by PROC IMPORT after the data step that it wrote to read the data has finished to let you know that the data step had one or more errors.

 

You need to look in the log between those two messages to see the data step that PROC IMPORT generated and find the error messages that the data step generated.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 247 views
  • 3 likes
  • 3 in conversation