data cleaning from text file

[naveen-kumar11]

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

[Reeza]

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

[Ksharp · Accepted solution]

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;

[naveen-kumar11]

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

[Ksharp]

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.

[naveen-kumar11]

for example sponsor_name jijo joseph naveen kumar
output:
sponsor_name
jijo joseph
naveen kumar

[Ksharp]

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 ;

[naveen-kumar11]

thankyou sooo much for your answers.its working great