topic Re: Importing csv Text file with comma and double quote in SAS Data Management

Importing csv Text file with comma and double quote

mlogan — Wed, 15 May 2019 15:18:37 GMT

Hi All,

I tried to import data from a csv text file and failed to import successfully. Here is my code, can someone please help me with the correct code.

Sample data file is attached.

Thank You.

proc import datafile="C:\2019_05_14\CanNCOA2_Results01.txt"
out=Address.CanNCOA_Results
dbms=csv;
delimiter = ",";
getnames=yes;
guessingrows=100000;
run;

Re: Importing csv Text file with comma and double quote

Reeza — Wed, 15 May 2019 15:25:33 GMT

Remove the delimiter option and it should be fine. Quotes around character variables are part of the CSV standard. If that still doesn't work try changing guessingrows to MAX instead of 100000. Or write your data step code that's customized to the data.

Re: Importing csv Text file with comma and double quote

Kurt_Bremser — Wed, 15 May 2019 15:28:14 GMT

You have ambiguous column names, that is not valid. Have that fixed at the data source.

Re: Importing csv Text file with comma and double quote

Tom — Wed, 15 May 2019 15:30:10 GMT

The file looks normal to me. So your code should run.

Of course since you are using PROC IMPORT it will have to guess at what type of data is in each field.

You could just write your own data step to read the file instead. Then you will have total control over how the variables are defined.

Re: Importing csv Text file with comma and double quote

mlogan — Wed, 15 May 2019 15:30:23 GMT

Hi Reeza, It did not work. Can you please write a working code for me. Thanks.

Re: Importing csv Text file with comma and double quote

Reeza — Wed, 15 May 2019 15:36:02 GMT

Please explain what 'it did not work' means. Post your code and log.

Re: Importing csv Text file with comma and double quote

Kurt_Bremser — Wed, 15 May 2019 16:48:57 GMT

I ran this code

proc import
  datafile="$HOME/sascommunity/mlogan.txt" 
  out=test
  dbms=csv
  replace
;
run;

and it worked. It was just that the columns with repeated column names were imported as var27,var28 and so on.

If something "does not work", post the log (use the {i} button for this), or show how the results differ from your expectations.

Re: Importing csv Text file with comma and double quote

ballardw — Wed, 15 May 2019 16:57:52 GMT

@Tom wrote:

The file looks normal to me. So your code should run.

Of course since you are using PROC IMPORT it will have to guess at what type of data is in each field.

You could just write your own data step to read the file instead. Then you will have total control over how the variables are defined.

Or modify the code created by Proc Import.

There should be a data step in the LOG you can copy and modify after fixing the column name issue.

Since your example data is short I would suggest making the fields a bit wider, especially any that your example data set to $1 because the field was missing.

Re: Importing csv Text file with comma and double quote

Patrick — Thu, 16 May 2019 02:51:53 GMT

@mlogan

You can help us help you by always providing the SAS Log part which shows the first ERROR message.

Removing the delimiter statement as @Reeza proposes creates valid Proc Import syntax for a csv file.

Is the ERROR you get by any chance

ERROR: Physical file does not exist

If so then always be aware that any file you want to import via SAS code must be referenced as the machine "sees" it from where your SAS code executes. If you don't have a local SAS installation then that's SAS on your server machine and all paths must be as on your server - which is not your local C: drive.

If you're using SAS EG then there is an upload task to move a file from a local to a server environment and then execute Proc Import against the path on the server.

Even easier with EG is to use the Import task (File/Import) as this will both upload your data to the server and generate valid SAS code to read the data in one go.

I believe there are similar options available with SAS Studio.