topic Re: Importing csv file with columns containing special characters and multiple delimiters in SAS Programming

Importing csv file with columns containing special characters and multiple delimiters

kaziumair — Mon, 22 Feb 2021 06:42:11 GMT

Hi everyone ,

I have to convert a CSV file into a SAS Dataset . The CSV file has column names containing special characters like '/' and '-' and spaces .The file contains column names like 'E/I','Sub-Category' . There are also multiple delimiters like commas ,tilde used in the file. I am using data step and the infile statement to specify multiple delimiters .

I used the validvarname=v7 option before the data step , however it treats the 'Sub-Category' column as as number list and gives the following error "Missing numeric suffix on a numbered variable list (sub-category)".

How do I import this file?

Re: Importing csv file with columns containing special characters and multiple delimiters

japelin — Mon, 22 Feb 2021 06:59:45 GMT

Can you provide a csv and code to reproduce the error?
"Sub-category" is being treated as a variable.
You can't use "-" in SAS variable names.

It represents a list of numbers.

Re: Importing csv file with columns containing special characters and multiple delimiters

kaziumair — Mon, 22 Feb 2021 07:15:05 GMT

Hi ,thank you for responding.

I cannot provide the original file , created a dummy file containing column sub-category.

Also attaching the code

Re: Importing csv file with columns containing special characters and multiple delimiters

japelin — Mon, 22 Feb 2021 07:40:12 GMT

You can't use a space or - in a variable name.
Since csv is quoted, use the dsd option.
The first line is not data, so use firstobs to import from the second line.

how is this code.

data demo_test;
  length id 8 firstname $7 lastname $10 department $10 dateofjoining 8 subcategory $40;
	infile "C:\Users\test\df-demo.csv" dsd firstobs=2;
	input id 
		  firstname  
		  lastname  
		  department  
		  dateofjoining 
		  subcategory ;
	informat dateofjoining mmddyy10.;
	format dateofjoining date9.;
run;

Re: Importing csv file with columns containing special characters and multiple delimiters

kaziumair — Mon, 22 Feb 2021 07:48:15 GMT

I think the solution is that I will have to change the column name to adhere to sas naming conventions like the import wizard in EG does with 'Rename columns to comply with sas naming conventions' option . I am unable to figure out how I can do this with data step .

Re: Importing csv file with columns containing special characters and multiple delimiters

japelin — Mon, 22 Feb 2021 08:24:24 GMT

The variable names I wrote follow the SAS rules, so I don't think you need to rename them.

If you wanna rename in data step, rewrite variable name both after length and input statement.