topic Re: proc import problem with column length in SAS Programming

proc import problem with column length

Vendy — Fri, 15 Jul 2016 09:49:57 GMT

Hi,

I would like to import csv to sas table but I have problem with column length. It seems that column data type(length) is set according to the first row.

For example the csv file(name.csv) looks like this:

    +---------+-------------+
    | cust_id | Name        | 
    +---------+-------------+
    |       1 | King        | 
    |       2 | Smith       | 
    |       3 | Brown       |

I will use this code for import:

proc import datafile="\\inputs\name.csv" 
 out=lib.tablename dbms=csv replace;
 getnames=yes;
run;

And the result in the lib.tablename is:

    +---------+-------------+
    | cust_id | Name        | 
    +---------+-------------+
    |       1 | King        | 
    |       2 | Smit        | 
    |       3 | Brow        |

The last letter in a second and third row is deleted... Is there any way how to format column in proc import? Or may I use something else than proc import?

Thanks

Re: proc import problem with column length

arodriguez — Fri, 15 Jul 2016 09:58:40 GMT

There is an option in proc import. Try to add before the run; GUESSINGROWS=X. This means that the format should be applied after X rows readed

Re: proc import problem with column length

Vendy — Fri, 15 Jul 2016 10:17:18 GMT

Thank you very much. It works 🙂

Re: proc import problem with column length

RW9 — Fri, 15 Jul 2016 10:45:23 GMT

As an alternaitve, take responsibility for setting the data strcuture yourself - being the best person to know what that should be like, and don't rely on SAS proc import to guess what the data should look like.

data want;
  infile "<your csv file>.csv";
  length var1 $200 var2 8;
  format var1 $200 var2 8.;
  informat var1 $200 var2 8.;
  input var1 $ var2;
run;

You can get the actual program generated by proc import from your log, then you can modify that to meet your needs (or modify it to your data import agreement which details the specification of the imported data).

Re: proc import problem with column length

mlin828 — Fri, 25 May 2018 21:39:24 GMT

This method does not work for me, the log said unsuccessful.

my code is as following:

proc import datafile="\\sf...... ..csv"
out=adeg DBMS=csv replace;
GUESSINGROWS=2;
run;

Re: proc import problem with column length

mlin828 — Fri, 25 May 2018 21:40:20 GMT

yeah, by revising log code, the column length could be adjusted. Thanks!

Re: proc import problem with column length

mlin828 — Fri, 25 May 2018 21:46:57 GMT

revised the code to gussingrows=42 , then it works, thanks! it needs to be the row which has longest word, got it!