Please help me understand this PROC IMPORT issue - csv

[gsouther]

I have been working on using proc import rather than using the gui and ran across a strange (at least to me) issue. I was provided a csv file which was exported from Active Directory and only consisted of the distinguishedname field, which has commas within the field. It looks like this sample:

"distinguishedname"
"CN=servername,OU=Domain Controllers,DC=DC1234,DC=info,DC=com"
"CN=servername1,OU=Standard Servers,DC=DC1334,DC=info,DC=com"
"CN=servername2,OU=IIS,OU=Standard Servers,DC=DC1434,DC=info,DC=com"
"CN=servername3,OU=IIS,OU=Standard Servers,DC=DC1534,DC=info,DC=com"
"CN=servername4,OU=Standard Servers,DC=DC1634,DC=info,DC=com"
"CN=servername5,OU=Standard Servers,DC=DC1734,DC=info,DC=com"
"CN=servername6,OU=IIS,OU=Standard Servers,DC=DC1834,DC=info,DC=com"
"CN=servername7,OU=IIS,OU=Standard Servers,DC=DC1934,DC=info,DC=com"

using this proc import statement:

proc import datafile='myfile.csv' dbms=csv
out=work.procimportexcel replace;
getnames=yes;
run;

I get this output:

distinguishedname
"CN=servername
"CN=servername1
"CN=servername2
"CN=servername3
"CN=servername4
"CN=servername5
"CN=servername6
"CN=servername7

I thought it had something to do with the length of the variable name, but it I tried various things and could not get it to include the full field. it always cut it off at the first comma.

When I tried importing a very similar file, the only difference being additional columns, everything worked fine. So i added a column to my original file and just put some bogus data in it:

distinguishedname,test1
"CN=servername,OU=Domain Controllers,DC=DC1234,DC=info,DC=com",345
"CN=servername1,OU=Standard Servers,DC=DC1334,DC=info,DC=com",346
"CN=servername2,OU=IIS,OU=Standard Servers,DC=DC1434,DC=info,DC=com",348
"CN=servername3,OU=IIS,OU=Standard Servers,DC=DC1534,DC=info,DC=com",349
"CN=servername4,OU=Standard Servers,DC=DC1634,DC=info,DC=com",350
"CN=servername5,OU=Standard Servers,DC=DC1734,DC=info,DC=com",351
"CN=servername6,OU=IIS,OU=Standard Servers,DC=DC1834,DC=info,DC=com",352
"CN=servername7,OU=IIS,OU=Standard Servers,DC=DC1934,DC=info,DC=com",353

This file imports fine too without changing the proc import code at all. I get the full value of the first column, with no leading quotes as well as the second column.

Can someone help me understand why the file with only one column is not importing correctly? It seems that when there is only one field and it contains commas within the quotes, SAS doesn't understand to put it all in one field and wants to use the commas as delimiters. When there is more than one field it seems to know to ignore the commas within quotes and treat the commas outside of the quotes as delimiters.

Thanks.

[Kurt_Bremser]

proc import has its limitations, which become obvious here.

You see, it is a guessing procedure, and sometimes it just proves the old adage that computers are dumb.

Write the data step yourself (a very easy one here, with just one column), and everything's fine.

[ballardw]

That is not the result I get. When I create a text file with you example data by copy and paste and then run the same proc import pointing to that data set I get a 71 character field with the complete text.

However, if I delete all of the quotes at the end of each dataline I get exactly what you show.

So I suspect that may indicate something done to your file.

[Ksharp]

Maybe you should define your delimiter,since your delimiter doesn't look like comma.

proc import datafile='myfile.csv' dbms=csv
out=work.procimportexcel replace;
delimiter=' ';
getnames=yes;
run;