BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
naveen-kumar11
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

7 REPLIES 7
Reeza
Super User

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

Ksharp
Super User

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5
for example sponsor_name jijo joseph naveen kumar
output:
sponsor_name
jijo joseph
naveen kumar
Ksharp
Super User

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
Calcite | Level 5
thankyou sooo much for your answers.its working great

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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