Problem importing csv file with semicolons into SAS

[WhiteDiamond]

I'm trying to import a csv file into SAS. I'm using the Base SAS 9.2

The file, that I need to use, contains: 41188 number of Instances and 21 number of attributes (20 + output attribute). When I try to import the file, SAS tells me that "Problems were detected with provided names".

In fact in the final output I see that the file has not been imported correctly. I was searching for a solution on the Internet, but I didn't succed. That's why I hope that someone in these community can help me. Thanks for your time.

 

[RW9]

Your problem lies with variable names like "emp.var.rate".  These are not valid SAS variable names hence the proc import - which I presume you are using - fails.  Consider doing one of the following:

- If you need to use proc import - then manually change the names of columns in the CSV file.

- Write a datastep program to import the data, star data at observation 2 and specify column names yourself.  This would always be my preferred method:

data want;
  infile "your_data.csv" firstobs=2;
  input age 
          job $
          education $
          default $
...;
run;

Alll of the variables and their formats/informats/lengths will be specified in the data import agreement you have for this data import.  If not, then a good start would be to get one.  Otherwiese you have to guess from the file.

[data_null__]

---

@RW9 wrote:

Your problem lies with variable names like "emp.var.rate".  These are not valid SAS variable names hence the proc import - which I presume you are using - fails.  

emp.var.rate is valid with VALIDVARNAME=ANY the default in EG,.  The real problems as already noted is failure to specify the delimiter with PROC IMPORT.

34         proc options option=validvarname;
35            run;
    SAS (r) Proprietary Software Release 9.4  TS1M3

 VALIDVARNAME=ANY  Specifies the rules for valid SAS variable names that can be created and processed during a SAS session.
36         data _null_;
37            "emp.var.rate"n='Am I valid';
38            put _all_;
39            run;
emp.var.rate=Am I valid _ERROR_=0 _N_=1
40         

If validvarname=V7 proc import will atempt to create valid names.

options validvarname=v7;
Filename FT15F001 temp;
proc import datafile=FT15F001 out=test dbms=dlm replace;
   delimiter=';';
   parmcards4;
emp.var.rate;Other name
1;2
;;;;
   run;

[ballardw]

It really looks like your data is semicolon delimited not comma delimited. Try setting the delimiter to ; .

[Reeza]

Your file isn't CSV  (comma separated values), your dlm=";". 

If you look in the generated code you can see the delimiter was set to a comma.

You can use the approach @RW9 suggested, but add in the DLM=";"

[WhiteDiamond]

First of all, thanks to all of you for your time and for helping me. Finally I imported the file into SAS. However, the lenght of the columns is not the right one. Is there any way to modify once the file has been imported or it's better to define the length from the very beginning (before importing)? 

[ballardw]

You don't show how you imported this.

You might try proc import with DLM=';'. The log should have code for a data step that SAS generated to read the data set.

You could copy that and change appropriate informat statements or use LENGTH statements prior to the informat to set lengths.

It may be that you should copy a the header and a few rows, 10 or so, into a text file and post that. It appears that you still have something odd as you shouldn't have all of those quotes hanging around.

[WhiteDiamond]

This is the proc import that I used to import the file and this is the log. 

[Reeza]

SAS assumes that character variables have a length of 8, which is why you're seeing truncation.

Use Proc import on the file and get the code that is generated. Then modify that manually.

OR

Add to your code, informat/format statements for variables that are longer than 8 characters, the following assigns a format 20 characters to the variables.

informat var_trunc $20.;
format var_trunc $20.;

[Ksharp · Accepted solution]

options validvarname=v7;
Filename FT15F001 'c:\temp\have.txt';
proc import datafile=FT15F001 out=test dbms=CSV replace;
   delimiter=';';run;

[WhiteDiamond]

Thank you very much for your help. You definitely solved my problem.