topic Re: data cleaning from text file in SAS Data Management

data cleaning from text file

naveen-kumar11 — Sat, 11 Jun 2016 17:21:11 GMT

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

Re: data cleaning from text file

Reeza — Sat, 11 Jun 2016 20:21:22 GMT

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

Re: data cleaning from text file

Ksharp — Sun, 12 Jun 2016 02:46:45 GMT

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;

Re: data cleaning from text file

naveen-kumar11 — Sun, 12 Jun 2016 03:05:13 GMT

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

Re: data cleaning from text file

Ksharp — Sun, 12 Jun 2016 03:29:30 GMT

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.

Re: data cleaning from text file

naveen-kumar11 — Sun, 12 Jun 2016 03:33:33 GMT

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

Re: data cleaning from text file

Ksharp — Sun, 12 Jun 2016 03:39:35 GMT

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 ;

Re: data cleaning from text file

naveen-kumar11 — Sun, 12 Jun 2016 03:42:32 GMT

thankyou sooo much for your answers.its working great