Proc import, GETNAME changes the value of numeric columns

[sun538]

Hi,

I have a CSV data set without a header row (no colnames). What I tried to do was to import data using "getnames=no" and then use "rename" option to change column names. however I realized that my numeric columns are totally destroyed while importing and it changes the value and format :

proc import datafile= "\\...\DATA.csv"
	out=mydata
	dbms=csv
	replace;
	getnames=no;
run;

The numeric columns are changed in value and format as a result ( those " .00" records have a value in csv data set )

Could you please let me know how to import my data (which does not have header) while keeping the format and values?

Thank you.

[Kurt_Bremser]

Please post a sample of your file by opening it with a text editor (not Excel!) and copy/pasting a few lines into a window opened with the </> button.

[sun538]

Thanks for your response. I check the data in text editor and it displays the data same as the results of proc import ie ".00" (  a row of data in text editor: "B","No Broker",".07",".00",".00").  However, when I open it with excel, it shows normal values. Do you have any idea why that happens and how to fix it in SAS? 

[ballardw]

---

@sun538 wrote:

Thanks for your response. I check the data in text editor and it displays the data same as the results of proc import ie ".00" (  a row of data in text editor: "B","No Broker",".07",".00",".00").  However, when I open it with excel, it shows normal values. Do you have any idea why that happens and how to fix it in SAS? 

---

Basically Import is a guessing procedure. It will assume than any variable that is enclosed in quotes is supposed to be character.

So stupid programs that export numeric values as text don't play well with Proc Import.

Solution: Look at the Log after using import. You will find data step code that the procedure wrote to read the data.

You can copy that from from the log and paste into program editor. Clean it up by removing line numbers. Change the INFORMAT statements that likely look like: Informat Var19 $9. ;

to an informat that will read numbers, likely Informat Var19 32. ; will work just fine. You could also at this time pick meaningful variable names.

Large economy sized hint: Run the Proc Import code with the option: Guessingrows = max; or a largish number in relation to the number of records in your data. The fit generally gets better.

Here is example of reading the SAME data in quotes but with different informats:

data example1;
   infile datalines dlm=',' dsd ;
   informat var1 $10. Var2 $10.;
   input var1 var2;
datalines;
"123.45",".00"
"","999999"
;

data example2;
   infile datalines dlm=',' dsd ;
   informat var1 10. Var2 10.;
   input var1 var2;
datalines;
"123.45",".00"
"","999999"
;

Note that the second data set will have numeric values.

Excel has it's own "not plays well with others" behavior by making different assumptions, such as turning character values like bank account numbers that often start with zero(s) and assumes they should be numeric.

NEVER save a CSV file that Excel has opened because you can get some very bad behaviors in resulting values.

[sun538]

Thanks for taking the time and your help. I followed your solution and used the syntax from log, cleaning up and changing the format for those mentioned columns according to your suggestion. However, it returned all values on those columns null.

[ballardw]

Provide details of what you actually did.

Are there errors in the log?: Post the code and log in a code box opened with the <> to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

[Kurt_Bremser]

You really do not use PROC IMPORT for csv files, and especially not for files with such a simple structure.

data want;
infile datalines dlm="," dsd truncover;
input var1 $ var2 :$10. var3 var4 var5;
datalines;
"B","No Broker",".07",".00",".00"
;

As you can see, the code is no miracle at all, and quickly written.

[Tom]

---

@sun538 wrote:

Thanks for your response. I check the data in text editor and it displays the data same as the results of proc import ie ".00" (  a row of data in text editor: "B","No Broker",".07",".00",".00").  However, when I open it with excel, it shows normal values. Do you have any idea why that happens and how to fix it in SAS? 

---

PROC IMPORT will assume that if every value in a column has quotes around it that the column is character.  Do you know why your CSV file has all of those unnecessary quotes? 

Just read the data yourself, especially if you really only have 5 columns it is probably easier to write the data step code than the PROC IMPORT code. Plus you can use the names you want right away and avoid the extra work to rename them.

data want;
  infile 'myfile.csv' dsd truncover ;
  input type :$5. status :$12.  num1-num3 ;
run;

If your file has a lot more columns and you really have no idea which columns or numbers and which are character strings you might get better results from PROC IMPORT if you first make a copy of the file without those unneeded quotes.

filename copy temp;
data _null_;
  infile 'myfile.csv' dsd truncover length=ll column=cc ;
  file copy dsd;
  do until(cc>ll);
     input value :$32767. @;
     put value @;
  end;
  put;
run;
proc import datafile=copy .....