importing untruncated character variable from csv file

[ama220]

Folks, I need help with importing character variables that do not have truncated values from csv files. Below is my import code and the lenght of the variable I need to reach. Any help with resolving this issue would be much appreciated!

PROC IMPORT OUT= TEST.my_data_2

            DATAFILE= "C:\testing\drugx\data\rhabdomyo.csv"

            DBMS=CSV REPLACE;

     GETNAMES=YES;

     DATAROW=2;

RUN;

Variable	Type	Len	Format	Informat
Drugname	Char	13	$13.	$13

[SASKiwi · Accepted solution]

PROC IMPORT OUT= TEST.my_data_2
            DATAFILE= "C:\testing\drugx\data\rhabdomyo.csv"
            DBMS=CSV REPLACE;
     GETNAMES=YES;
     DATAROW=2;
    guessingrows = max;
RUN;

Try GUESSINGROWS.

[ama220]

Thanks, it worked!

[ama220]

Thanks, it worked!

[Tom]

If you know how the variables should be defined why are using PROC IMPORT.  That will have to GUESS how to define the variables.

In general it is best to write a data step to read a CSV file.  That will give you complete control over the variable, NAME, TYPE, storage LENGTH, and whether or not to attach any formats, informats or labels.  Note that most variables do not need either FORMAT or INFORMAT attached to them since SAS already knows how to write and read both numeric and character variables.  The main exception is DATE, TIME and DATETIME values.

[ama220]

Thank you! The file has many variables and I did not wan to list them all in in the input line. Do you know an easy way to do that without having to type them all?

[Tom]

---

@ama220 wrote:
Thank you! The file has many variables and I did not wan to list them all in in the input line. Do you know an easy way to do that without having to type them all?

---

Copy and paste.

If you have trouble opening the CSV file in a text editor to get the header row out so you can copy it then let SAS do it for you.

data _null_;
  infile csv dsd obs=1;
  input name :$32. @@;
  put name @;
run;

Now you can copy the names from the SAS log and the commas will have already been removed.

Once you have the list of names you can use it to make a LENGTH statement.  Just put the length you want after each variable (or group of variables if they all should be the same length).  Then the INPUT statement can just use a simple positional variable list.

data want;
  infile csv dsd truncover firstobs=2;
  length varfirst $20 var2 var3 var4 8 var5 varlast $10 ;
  input varfirst -- varlast;
run;

If you have any DATE, TIME or DATETIME values then also add INFORMAT and FORMAT statements for those variables. (Normal numeric and character variables do not need either a format or an informat attached to them.)

Another possibly is to paste the list into the INPUT statement.  You can then add :$xx. after any character variable.  If you are happy with making a character variable of length 8 just use bare $ instead.

data want;
  infile csv dsd truncover firstobs=2 ;
  input varfirst :$20. var2 var3 var4 var5 :$10. varlast :$10. ;
run;

[Kurt_Bremser]

NEVER use PROC IMPORT when you don't have to.

For csv files, write the DATA step yourself, according to the description/documentation of the file.

The IMPORT procedure makes guesses, and you regularly end up with incorrect data types and other attributes, which you then have to fix.

[ama220]

Thanks!

[ama220]

Folks, I need help with importing character variables that do not have truncated values from csv files. Below is my import code and the lenght of the variable I need to reach. Any help with resolving this issue would be much appreciated!

PROC IMPORT OUT= TEST.my_data_2

            DATAFILE= "C:\testing\drugx\data\rhabdomyo.csv"

            DBMS=CSV REPLACE;

     GETNAMES=YES;

     DATAROW=2;

RUN;

Variable	Type	Len	Format	Informat
Drugname	Char	13	$13.	$13