reading comma delimited text file into sas data sets

[deleted_user]

Hi
any one can help me to sort out this problem.


i have comma delimited text fie , inthat text file one variable values contains comma as a value. while i read that values it is giving data errors

ex:
id,name,location,salary
101, john,newyork,usa,40000

the text file data is like above.
how can i convert text file into datasets

[GertNissen]

Get your supplier of the textfile to deliver all text with " - like this

id,name,location,salary
101, "john","newyork,usa",40000

then you can use the infile dsd option.

[deleted_user]

thank you for giving response.

is there any alternate for this problem?.

[data_null__]

Not and easy one. A CSV without quoted delimiters when they appear within a field is NOT a CSV file. You could call it junk.

It could be salvaged by counting delimiters if there is some pattern that can be recognized.

you only show one line are they all like that or just a few?

Show more data.

[sbb]

Not really, unless you are willing to read up the input data row and parse each field into a SAS variable using DATA step functions such as SCAN, SUBSTR, FIND and INDEX/INDEXW. Still it will be a crap-shoot unless you know what to expect. I'd agree that the input data-format is unreasonable and should be corrected to solve the problem, rather than "attempting" to address the symptom.

Scott Barry
SBBWorks, Inc.

[Alankar]

Sairam,

after import the data use below code

data infile (drop = location1 location2);
set infile;
location = trim(locatio1)||', '||location2;
run;

Regards
Alankar

[Alankar]

before following the above step, we should import location column as 2 variables (location1 & location2).

[GertNissen]

Hi Alankar

How would you handle a situation where you have the follwing data

id,name,location,salary
101, john,newyork,usa,40000
101, john,newyork usa,40000
101, john,10004,newyork,usa,40000

If you use location = trim(locatio1)||', '||location2 ?

[Alankar]

data in txt file is like this :

id,name,location,salary
101,john,newyork,usa,40000
101,john,newyork,usa,40000
101,john,newyork,usa,40000

data import;
infile "C:\Documents and Settings\am66635\Desktop\test.txt"
dlm = "," firstobs = 2;
input id $ name $ location1 $ location2 $ salary;
run;

data import;
infile "C:\Documents and Settings\am66635\Desktop\test.txt"
dlm = "," firstobs = 2;
input id $ name $ location1 $ location2 $ salary;
run;

data clean (drop = location1 location2);
set import;
location = compress(location1||","||location2);
/*location = trim(location1||","||location2);*/
run;

This is based on my understanding.

as you said if the data like following, it is difficult to import(3rd observation is different, right?).

id,name,location,salary
101,john,newyork,usa,40000
101,john,newyork usa,40000
101,john,10004,newyork,usa,40000