BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SaraDianaPasik
Fluorite | Level 6

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.
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

5 REPLIES 5
PeterClemmensen
Tourmaline | Level 20

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 files:)

SaraDianaPasik
Fluorite | Level 6
 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.
 
Tom
Super User Tom
Super User

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.

 

SaraDianaPasik
Fluorite | Level 6

This worked, thanks a million!

Tom
Super User Tom
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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