DATA Step, Macro, Functions and more

Problem importing csv file with semicolons into SAS

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Problem importing csv file with semicolons into SAS

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.

 

 

output.jpg

 

 

 

 

log.jpg 

csv file with semicolons.png


Accepted Solutions
Solution
‎03-05-2016 03:27 AM
Super User
Posts: 9,676

Re: Problem importing csv file with semicolons into SAS

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

View solution in original post


All Replies
Super User
Super User
Posts: 7,401

Re: Problem importing csv file with semicolons into SAS

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.

Respected Advisor
Posts: 3,777

Re: Problem importing csv file with semicolons into SAS


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;
Super User
Posts: 10,497

Re: Problem importing csv file with semicolons into SAS

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

Super User
Posts: 17,818

Re: Problem importing csv file with semicolons into SAS

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=";"

 

 

Occasional Contributor
Posts: 5

Re: Problem importing csv file with semicolons into SAS

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)? 

 

length.jpg

 

Super User
Posts: 10,497

Re: Problem importing csv file with semicolons into SAS

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.

Occasional Contributor
Posts: 5

Re: Problem importing csv file with semicolons into SAS

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

 

code.jpglog2.jpg

Super User
Posts: 17,818

Re: Problem importing csv file with semicolons into SAS

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.;

 

Solution
‎03-05-2016 03:27 AM
Super User
Posts: 9,676

Re: Problem importing csv file with semicolons into SAS

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

Re: Problem importing csv file with semicolons into SAS

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 1031 views
  • 5 likes
  • 6 in conversation