sas proc import txt wth delimiter inside observations

Question

My first question as a new user of SAS 9.3. I want to use proc import to read a large text file with dlm=’,’. But there is one variable has “,” in between in some obs, eg. “Hartford, CT”. (not all of them, others like “XL Center”).IS there any way that I can read “Hartford, CT” into one variable just like “XL Center” while using proc import to this text file? Many thanks

Edited here: sorry I shouldn't put quote around the record. there are NO quotes wrap on any record, be XL center, or Hartford, CT. when dlm set as comma, the row has Hartford, CT produces on extra column and shifts records into wrong column afterwards.

Are you sure the delimiter isn't really '","', like "Hartford, CT","F","43"? That is normal in Norway. — Stig Eide
Here is a blog that give an other example, where the field that has commas is surrounded by quotes: blogs.sas.com/content/sastraining/2010/11/19/… — Stig Eide
Thanks for posting to the trick. Works like a charm though my question remains, since there are no quotes wrap in my case. — YXZ

Allan Bowe Allan Bowe · Accepted Answer · 2014-11-14T07:25:57

So long as your text file has quotes around the delimeter, then it will work automatically. For example:

/* example data */
data _null_;
   file "%sysfunc(pathname(work))/some.csv"; 
   put 'head1,head2,head3';
   put 'XL Center,1,"Hartford, CT"';
run;

/* import */
proc import datafile="%sysfunc(pathname(work))/some.csv" 
     out=example
     dbms=dlm
     replace;
     delimiter=",";
     datarow=2;
run;

enter image description here

sas proc import txt wth delimiter inside observations

1 Answers