topic Re: Importing issue in csv file in SAS Programming

Importing issue in csv file

SaraDianaPasik — Sun, 05 Nov 2017 19:10:28 GMT

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.

Re: Importing issue in csv file

PeterClemmensen — Sun, 05 Nov 2017 19:26:31 GMT

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

Re: Importing issue in csv file

Tom — Sun, 05 Nov 2017 19:29:20 GMT

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.

Re: Importing issue in csv file

SaraDianaPasik — Sun, 05 Nov 2017 19:30:36 GMT

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.

Re: Importing issue in csv file

Tom — Sun, 05 Nov 2017 19:45:44 GMT

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&docsetVersion=9.4&locale=en

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.

Re: Importing issue in csv file

SaraDianaPasik — Sun, 05 Nov 2017 19:55:39 GMT

This worked, thanks a million!