topic Re: Proc Import Generates "Error" from CSV Import in SAS Programming

Proc Import Generates "Error" from CSV Import

A_SAS_Man — Tue, 12 Jul 2022 18:33:50 GMT

I am importing a csv file into SAS as a part of a larger process. I am using the code below.

proc import datafile="/data/data.csv" 
out=test
dbms=csv replace;
GUESSINGROWS=MAX;
 run;

When I run this, it generates the following Note, but it seems to act as an error would because it makes my process stop and it is underlined in red. But again, I have no "Errors" when I look at my log summary just a few warnings and notes.

In addition, even though it stops my process it actually successfully generates the output data set. Can anyone help me understand what is going on with this and how I could potentially fix it?

Edit: The entire log is very long but I believe based on @ballardw's comment I have found the more relevant part. It seems as though multiple column names are being truncated and coming in with the same name, so after the first instance of that column it generates an error. See below extended log information.

172               informat PresenceOfDiabetesType2InsulinDe best32. ;
173               informat "CurrentlyBeingTreated-DiabetesTy"N $1. ;
174               informat PresenceOfDiabetesType2NonInsuli best32. ;
NOTE: SCL source line.
175               informat "CurrentlyBeingTreated-DiabetesTy"N best32. ;
                                                               _______
                                                               485
NOTE 485-185: Informat $BEST was not found or could not be loaded.

176               informat PresenceOfDiabetesType1 best32. ;
NOTE: SCL source line.
177               informat "CurrentlyBeingTreated-DiabetesTy"N best32. ;
                                                               _______
                                                               485
NOTE 485-185: Informat $BEST was not found or could not be loaded.

Farther up in the log you can see this as well:

Name CurrentlyBeingTreated-DiabetesType2Insulin truncated to CurrentlyBeingTreated-DiabetesTy.
Name PresenceOfDiabetesType2NonInsulinDependent truncated to PresenceOfDiabetesType2NonInsuli.
Name CurrentlyBeingTreated-DiabetesType2NonInsulin truncated to CurrentlyBeingTreated-DiabetesTy.
Name CurrentlyBeingTreated-DiabetesType1 truncated to CurrentlyBeingTreated-DiabetesTy.

So thank you for potentially finding that issue, is there a way to modify proc import to allow longer variable names or something along those lines? These files are provided from a vendor so I have no control over the naming conventions and am trying to keep this as automated as possible.

Re: Proc Import Generates "Error" from CSV Import

ballardw — Tue, 12 Jul 2022 16:21:07 GMT

How about showing the entire log of the data step generated and executed by Proc Import.

That specific message means that the variable has already been declared as character and as such a numeric informat, or format, cannot be used for that variable. That is what the $best indicates, that a character variable is used.

Re: Proc Import Generates "Error" from CSV Import

Reeza — Tue, 12 Jul 2022 16:25:03 GMT

That would be a really weird error for a standard proc import code to generate. Did you modify the code generated and then get those errors?

Re: Proc Import Generates "Error" from CSV Import

A_SAS_Man — Tue, 12 Jul 2022 16:48:18 GMT

I'm not really sure what you mean, I ran the code above in my original post and it generated that output.

Re: Proc Import Generates "Error" from CSV Import

Reeza — Tue, 12 Jul 2022 16:49:38 GMT

Definitely need to show the full log as @ballardw requested.

Re: Proc Import Generates "Error" from CSV Import

A_SAS_Man — Tue, 12 Jul 2022 17:01:02 GMT

Thank you, I posted more information from the log which I think gets at what you are saying.

Re: Proc Import Generates "Error" from CSV Import

Reeza — Tue, 12 Jul 2022 17:05:29 GMT

I think for a file with these type of issues I'd copy the code from the log, manually modify it to be correct including renaming variables as needed and use that to import your data instead of PROC IMPORT. This is a better approach for an automated process anyways as it will also ensure that your variables are always read correctly and the same type/format which is not guaranteed with PROC IMPORT.

Re: Proc Import Generates "Error" from CSV Import

ballardw — Tue, 12 Jul 2022 17:08:02 GMT

Start in the log at the Proc Import line. Copy everything through the the notes at the end of the run about observations read.

Paste all of that into a text box.

The note about SCL code often means that something with a dot is used incorrectly but Proc Import generally doesn't do that.

The addition of truncated variable names is not related.

I would also suggest setting: Options validvarname=v7; to stop creating those name literal variables with hyphens, spaces and other non-standard characters in the variable names. It is going to be bad enough dealing with those extremely long variable names without adding the requirements to repeatedly address the "long var name"N literal on top.

Re: Proc Import Generates "Error" from CSV Import

A_SAS_Man — Tue, 12 Jul 2022 17:11:30 GMT

It should now be attached to my original post.

Re: Proc Import Generates "Error" from CSV Import

A_SAS_Man — Tue, 12 Jul 2022 17:12:02 GMT

I didn't realize proc import was just using a data step. That is really helpful, going to do some testing on that now.

Re: Proc Import Generates "Error" from CSV Import

A_SAS_Man — Tue, 12 Jul 2022 17:23:56 GMT

The addition of that option actually solves the issue, repeat variable names are just imported formatted as "VAR131" or similar.

Re: Proc Import Generates "Error" from CSV Import

ballardw — Tue, 12 Jul 2022 17:27:15 GMT

The problem is stemming from extremely long column headings. Note that line 173 and 175 are attempting to assign different informats and formats to multiple columns with the same name after truncation to the allowed 32 characters. I have had similar data with long column headings but never used Proc Import with Validvarname=any, which allows use of the name literals. The Import generator often fixes some of this with similar names suffixed with 1,2,3 but very long names seem to be the exceptions You have the data data step; copy to the editor and change the names of one of the variables (in each pair, I think you have a fair number). You can drop most of the FORMAT statements and likely the Informats using BEST32. Just make sure that the Input statement has different variable names. If you remove informat statements the order of columns in the data set will change. If you are extremely careful with editor search and replace you can find the second of each of the Informat and Input occurrences of the name and change just that one. Save the code and rerun.

173               informat "CurrentlyBeingTreated-DiabetesTy"N $1. ;
174               informat PresenceOfDiabetesType2NonInsuli best32. ;
NOTE: SCL source line.
175               informat "CurrentlyBeingTreated-DiabetesTy"N best32. ;
                                                               _______
                                                               485
NOTE 485-185: Informat $BEST was not found or could not be loaded.

Re: Proc Import Generates "Error" from CSV Import

Reeza — Tue, 12 Jul 2022 17:28:04 GMT

Yup! It just generates the data step code. I often use it as my starting point to write code for large files (lazy/efficient) method IMO.

Then modify as necessary 🙂

I would use @ballardw suggestion to re-run with option validvarname=v7 to clean up some of those names as well.

FYI - if you're using SAS Foundation (not Studio) you can copy the code without the line numbers by holding down ALT + CNTRL while selecting the code. I use Studio so use NotePad++ to remove the line numbers these days.

Re: Proc Import Generates "Error" from CSV Import

A_SAS_Man — Tue, 12 Jul 2022 17:31:03 GMT

I am also using studio, can you expand on how you use notepadd++ to remove line numbers?

Re: Proc Import Generates "Error" from CSV Import

Reeza — Tue, 12 Jul 2022 17:35:10 GMT

https://community.notepad-plus-plus.org/topic/15203/slick-way-to-remove-line-numbers-from-text

Re: Proc Import Generates "Error" from CSV Import

Tom — Tue, 12 Jul 2022 18:10:57 GMT

You have discovered a bug (another bug) in PROC IMPORT. It is triggered when using VALIDVARNAME=ANY and you have two non-standard names that are not unique in the first 32 bytes.

You can avoid it by setting the system option VALIDVARNAME=V7 .

Here is re-producible code to demonstrate the error.

options parmcards=csv;
filename csv temp;
parmcards;
This_name_is too_long_to_be_a_NAME1,This_name_is too_long_to_be_a_NAME2
1,Albert
2,Alice
;

options validvarname=v7;
proc import datafile=csv out=v7 dbms=csv replace ;
run;
options validvarname=any;
proc import datafile=csv out=any dbms=csv replace ;
run;
options validvarname=v7;

Or you could use a smarter tool to generate the code to read the file. Like https://github.com/sasutils/macros/blob/master/csv2ds.sas

filename csv2ds url "https://raw.githubusercontent.com/sasutils/macros/master/csv2ds.sas";
filename parmv url "https://raw.githubusercontent.com/sasutils/macros/master/parmv.sas";
%include parmv;
%include csv2ds;
%csv2ds(csv,out=csv2ds,replace=1)

Which will try to make clearer variable names and much clearer SAS code.

      Files\_TD24420_AMRAPY3WVP0VKU0_\#LN00064.
1538 +data csv2ds;
1539 +  infile CSV dlm=',' dsd truncover firstobs=2 ;
1540 +  length This_name_is_too_long_to_be_a_NA 8
1541 +    This_name_is_too_long_to_be_a__1 $6
1542 +  ;
1543 +  label
1544 +    This_name_is_too_long_to_be_a_NA='This_name_is too_long_to_be_a_NAME1'
1545 +    This_name_is_too_long_to_be_a__1='This_name_is too_long_to_be_a_NAME2'
1546 +  ;
1547 +  input This_name_is_too_long_to_be_a_NA -- This_name_is_too_long_to_be_a__1 ;
1548 +run;

NOTE: The infile CSV is:
      Filename=C:\Users\ABERNA~1\AppData\Local\Temp\1\SAS Temporary Files\_TD24420_AMRAPY3WVP0VKU0_\#LN00063,
      RECFM=V,LRECL=32767,File Size (bytes)=92,
      Last Modified=12Jul2022:13:56:15,
      Create Time=12Jul2022:13:56:15

NOTE: 2 records were read from the infile CSV.
      The minimum record length was 7.
      The maximum record length was 8.
NOTE: The data set WORK.CSV2DS has 2 observations and 2 variables.