topic Re: reading comma delimited text file into sas data sets in SAS Programming

reading comma delimited text file into sas data sets

deleted_user — Fri, 17 Apr 2009 07:59:37 GMT

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

Re: reading comma delimited text file into sas data sets

GertNissen — Fri, 17 Apr 2009 08:07:30 GMT

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.

Re: reading comma delimited text file into sas data sets

deleted_user — Fri, 17 Apr 2009 08:12:09 GMT

thank you for giving response.

is there any alternate for this problem?.

Re: reading comma delimited text file into sas data sets

data_null__ — Fri, 17 Apr 2009 13:11:18 GMT

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.

Re: reading comma delimited text file into sas data sets

sbb — Fri, 17 Apr 2009 13:13:21 GMT

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.

Re: reading comma delimited text file into sas data sets

Alankar — Mon, 20 Apr 2009 02:59:37 GMT

Sairam,

after import the data use below code

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

Regards
Alankar

Re: reading comma delimited text file into sas data sets

Alankar — Mon, 20 Apr 2009 03:02:04 GMT

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

Re: reading comma delimited text file into sas data sets

GertNissen — Mon, 20 Apr 2009 12:31:21 GMT

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 ?

Re: reading comma delimited text file into sas data sets

Alankar — Tue, 28 Apr 2009 03:08:45 GMT

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