Desktop productivity for business analysts and programmers

"Transpose" data - EG 4.1

Not applicable
Posts: 0

"Transpose" data - EG 4.1


I have a table that contains claims with claim text. A claim appears multiple times (row) but the claim text per row is different. Some claims may have 2 rows while other have 4 or more. The table looks like this:

Claim Comment
123 text text text text
123 xxxxxx xx xxxx xxxxxx
456 gggggg gggggggg gggg
456 gggkkkkgg kkkkg kkkkllk
456 yyyy yyyyyy xxxx yxyxyx
456 uuu hhaha hyeee eyyeyye

What i want the outcome to be is
Claim Comment
123 text text text text xxxxxx xx xxxx xxxxxx
456 ggggg gggggggg gggg gggkkkkgg kkkkg kkkkllk yyyy yyyyyy xxxx yxyxyx uuu hhaha hyeee eyyeyye

I hope this makes sense. It would be preferable if the comments were in one cell, but if they are their own, it is ok too (as long as they are on the same row).

Super Contributor
Super Contributor
Posts: 366

Re: "Transpose" data - EG 4.1

Posted in reply to deleted_user
Hello Magnus,

This is a solution:
data r(rename=(t=Comment));
retain Claim t;
length t $500;
set i;
if First.Claim then t="";
t=CATX(' ',t,Comment);
if Last.Claim then output;
by Claim;
keep t Claim;
Ask a Question
Discussion stats
  • 1 reply
  • 2 in conversation