topic Re: problem with importing csv file in SAS Programming

problem with importing csv file

clarke — Sun, 02 Sep 2018 16:26:27 GMT

Hello, everyone

I am really struggling with importing a .csv file into SAS. The dataset has 3 variables, but when it imports, SAS says it has 1 variable. It puts 3 columns into 1. I tried putting in the data with a cards statement, but it gave me errors as there is had too many lines. I attached a picture of what the data looks like in Excel and what it looks like in SAS. The data originally had commas as decimals and I changed it to full stops but the problem still occurs. Please help me out. (I imported it by clicking "import data" in "file"). Thank you so much

Re: problem with importing csv file

Tom — Sun, 02 Sep 2018 17:21:50 GMT

The picture on the left is NOT a CSV file. It appears to be some type of spreadsheet software. A CSV file is just a text file.

But looking at the values in the SAS dataset is really looks like you do not have a "real" CSV (comma separated values) but instead a delimited file where the delimiter is a semi-colon.

The easiest solution is to just tell SAS that your delimiter is a semi-colon instead of a comma.

But for three columns it is even easier (and you will have complete control) if you just write the data step your self.

data want ;
  infile 'myfile.txt' dsd dlm=';' firstobs=2 truncover ;
  input group x1 x2 ;
run;

Re: problem with importing csv file

Reeza — Sun, 02 Sep 2018 17:46:43 GMT

Try PROC IMPORT with GUESSINGROWS=MAX and set the delimiter to a semi colon.

It sounds like you had a french source file which uses a ; instead of comma, and a comma where a decimal would be instead.

As Tom mentioned to work with a CSV file you're best off opening it with a text editor, NOT Excel because Excel interprets the data to present it, not always correctly but it doesn't give you the correct information to import it.

This will take a bit longer to run than Tom's solution but should work as well.

proc import out=want datafile="path to csv" dbms=dlm replace;
delimiter=';'; guessingrows=max;
run;

@clarke wrote:

Hello, everyone

I am really struggling with importing a .csv file into SAS. The dataset has 3 variables, but when it imports, SAS says it has 1 variable. It puts 3 columns into 1. I tried putting in the data with a cards statement, but it gave me errors as there is had too many lines. I attached a picture of what the data looks like in Excel and what it looks like in SAS. The data originally had commas as decimals and I changed it to full stops but the problem still occurs. Please help me out. (I imported it by clicking "import data" in "file"). Thank you so much

Re: problem with importing csv file

Kurt_Bremser — Sun, 02 Sep 2018 18:14:11 GMT

In a data step, use the semicolon as a delimiter, and the commax. informat for the numeric variables.