Transposing groups of data

Reply
Occasional Contributor
Posts: 16

Transposing groups of data

Hello ..Hope this is an easy question.. trying to create a data step that transposes data into columns as such.

 

I have a file that I loaded that has groups of data with spacing.. like this: This is how it looks.. I need have in column 1 -->Group 1, then recognize the word end and the second column is Group2 and all the name under group 2.. need to repeat this over and over.

 

Group 1

  Paul

  Jason

  Kevin

end;

 

Group 2

 Charlie

 steve

 damien

end;

Grand Advisor
Posts: 17,325

Re: Transposing groups of data

I'm not following, can you explain some more please. If you can include what you have and what you're trying to get that might help.

Occasional Contributor
Posts: 16

Re: Transposing groups of data

In my list there is one column.. I Need to transpose to many columns like this

 

Group 1   Group 2

Paul        Charlie 

Kevin       Dennis

Jason       Sam

 

 

I need a loop to recognize the word "end" and name the column to the right with the variable below end.. which is Group2.. and everything under Group 2 is in that column .. until the next word "end" shows up

 

Does that make sense?

Grand Advisor
Posts: 17,325

Re: Transposing groups of data

Not really.

Is it a text file or a SAS data set?

 

Post a sample that reflects exactly what your data looks like, and one that is what you're trying to get.

Esteemed Advisor
Posts: 5,198

Re: Transposing groups of data

I think I see what you want. But I'm having a harder time to understand why. What will you do with the data after the reformatting? Looks like you are trying to use SAS as a text layout formatting tool.
If you are going to use the result for reporting or as a lookup table, the output data set should have two variables: group and name.
Data never sleeps
Occasional Contributor
Posts: 16

Re: Transposing groups of data

The idea is to import a text file and format it into a readable format..yes I am using sas to format and will need to do this process weekly..there is 267 groupings in each file..what do u suggest?
Respected Advisor
Posts: 4,606

Re: Transposing groups of data

This should do it:

 

data grp;
length name $20;
infile datalines truncover;
input name&;
datalines4;
Group 1
  Paul
  Jason
  Kevin
end;
 
Group 2
 Charlie
 steve
 damien
end;
;;;;

data grp2;
set grp;
where name is not missing;
if name =: "end" then do; col + 1; line = 0; end;
else do; line + 1; output; end;
run;

proc sort data=grp2; by line col; run;

proc transpose data=grp2 out=grp3(drop=line _name_) prefix=col_; 
by line;
id col;
var name;
run;
PG
Grand Advisor
Posts: 9,576

Re: Transposing groups of data

Sounds like you need MERGE skill. Chech me ,Matt, Arthur.T  proposed.

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

Esteemed Advisor
Posts: 5,198

Re: Transposing groups of data

My sugestion is to do like @PGStats, but skip the sort/transpose step.
If you're not having a presorted source data, or if there are gaps in the group no sequence, you need another way of assigning line (or group which I would call it).
Data never sleeps
Ask a Question
Discussion stats
  • 8 replies
  • 420 views
  • 0 likes
  • 5 in conversation