DATA Step, Macro, Functions and more

reading comma delimited text file into sas data sets

Reply
N/A
Posts: 0

reading comma delimited text file into sas data sets

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
SAS Employee
Posts: 174

Re: reading comma delimited text file into sas data sets

Posted in reply to deleted_user
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.
N/A
Posts: 0

Re: reading comma delimited text file into sas data sets

Posted in reply to GertNissen
thank you for giving response.

is there any alternate for this problem?.
Respected Advisor
Posts: 3,799

Re: reading comma delimited text file into sas data sets

Posted in reply to deleted_user
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.
Super Contributor
Super Contributor
Posts: 3,174

Re: reading comma delimited text file into sas data sets

Posted in reply to deleted_user
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.
Contributor
Posts: 26

Re: reading comma delimited text file into sas data sets

Posted in reply to deleted_user
Sairam,

after import the data use below code

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

Regards
Alankar
Contributor
Posts: 26

Re: reading comma delimited text file into sas data sets

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

Re: reading comma delimited text file into sas data sets

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 ?
Contributor
Posts: 26

Re: reading comma delimited text file into sas data sets

Posted in reply to GertNissen
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
Ask a Question
Discussion stats
  • 8 replies
  • 258 views
  • 0 likes
  • 5 in conversation