SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

data cleaning from text file

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

data cleaning from text file

[ Edited ]

hello guys i need a help here. i am struck here.

i have datasetlike this in text file

 

id    12  13 14 15 16       17 18 19 20                       sponsor name    jijo  joseph         shashank vaibhav           company   mindtree     acc wipro infosys


type         analytics Vba      sas r                                                       codeno  frg7325      aw33 sfsdf34       sdfs5

 

place kormangala       mgroad                  mejestic

 

 

where it it not formatted properly.so i want this to be look like

id     sponsor name     company     type             code_no     place

12   jijo joseph                  mindtree     analytics      frg7325      kormangala 

13    shashank             acc              vba              aw33          mgroad

so on.............

please someone help me out in this i need this solution 

 

 

 

i have attached the dataset here someone check it and give me the solution please


Accepted Solutions
Solution
‎06-11-2016 11:35 PM
Super User
Posts: 10,044

Re: data cleaning from text file

[ Edited ]
Posted in reply to naveen-kumar11

That is totally messed up .

UPDATED.

 

 


data have;
infile '/folders/myfolders/test4.txt' recfm=n dlm='200D0A'x;
input temp : $100. @@;
run;

data temp;
 set have;
 if temp in ('id' 'sponsor_name' 'company' 'type' 'codeno' 'place') then group+1;
run;
proc transpose data=temp out=x(rename=(col1=id));
by group;
var temp;
run;
proc transpose data=x out=want(drop=_name_);
id id;
var col:;
run;

View solution in original post


All Replies
Super User
Posts: 19,862

Re: data cleaning from text file

Posted in reply to naveen-kumar11

How do you know what lines up with what in the text file?

Solution
‎06-11-2016 11:35 PM
Super User
Posts: 10,044

Re: data cleaning from text file

[ Edited ]
Posted in reply to naveen-kumar11

That is totally messed up .

UPDATED.

 

 


data have;
infile '/folders/myfolders/test4.txt' recfm=n dlm='200D0A'x;
input temp : $100. @@;
run;

data temp;
 set have;
 if temp in ('id' 'sponsor_name' 'company' 'type' 'codeno' 'place') then group+1;
run;
proc transpose data=temp out=x(rename=(col1=id));
by group;
var temp;
run;
proc transpose data=x out=want(drop=_name_);
id id;
var col:;
run;
New Contributor
Posts: 4

Re: data cleaning from text file

thankyou for the answer that works fine but i havetwo problems with it.

1.what if we have more columns because we cant type all the names if we have more than some 60 variables know.can we directly take the variable names from the datasets?

2.if the data consists firstname and last name how to overcome from that?

but thakyou for giving the reply

Super User
Posts: 10,044

Re: data cleaning from text file

Posted in reply to naveen-kumar11

1.what if we have more columns because we cant type all the names if we have more than some 60 variables know.can we directly take the variable names from the datasets?

 

No. You can't. How do know which word is the variable name, Do you have any pattern to match variable name ? 

Typing the variable name you want in the following code.

 

 if temp in ('id' 'sponsor_name' 'company' 'type' 'codeno' 'place') then group+1;

 

 

2.if the data consists firstname and last name how to overcome from that?

 

Give us an example ? Data and Output.

New Contributor
Posts: 4

Re: data cleaning from text file

for example sponsor_name jijo joseph naveen kumar
output:
sponsor_name
jijo joseph
naveen kumar
Super User
Posts: 10,044

Re: data cleaning from text file

Posted in reply to naveen-kumar11

No. You have to give us RULES.

Why not be ?

sponsor_name
jijo joseph naveen 
kumar

 

If there are always two words for each NAME, that would be easy.

Make a FLAG and transpose.

if mod(_n_,2)=1 then FLAG+1 ;

New Contributor
Posts: 4

Re: data cleaning from text file

thankyou sooo much for your answers.its working great
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 745 views
  • 1 like
  • 3 in conversation