Long-to-Wide handling duplicates using array processing

[mtsasuser]

I was transposing my data from Long to Wide.  It works for most of my data, except for on variable that has duplicates not cleared by the by statement. X=7 and A=W have two observations B=o and B=p.  I know that the array I have written selects the last line where X=7 A=W B=p and does not include the the other X=7 A=W.  Is there a way to write the code to include both B=o and B=p without creating multiple X's either rewriting the DO statement or something else?

X	A	B	C	D
1	q		ts	1
2	q		ts	1
2	r	p	ts	2
3	q		ts	1
3	r	p	ts	2
3	w		ts	3
4	q		ts	1
4	r	p	ts	2
4	w		ts	3
4	t	p	ts	4
5	q		ty	1
5	r	o	ty	2
5	w		ty	3
5	t	u	ty	4
5	v		ty	5
6	q		th	1
6	r	o	th	2
6	s	p	th	3
6	w	p	th	4
6	v		th	5
6	t		th	6
7	z		ts	1
7	w	o	ts	2
7	w	p	ts	2

data transposed (drop=A B C D) ;

array AA {*} $ A1-A7;

array BB {*} $ B1-B7;

array CC {*} $ C1-C7;

do until (last.X);

set rawdata;

by X;

  aa(d)= a;

  bb(d)=b;

  cc(d)=c;

end;

output;

run;

[M_Maldonado]

Hi,

I haven't had a chance to look at your code.

It looks like you might get better input from this community:

I hope it helps.

Miguel

[Astounding]

It looks like you need to replace the variable D with a better version that changes to "3" on the final observation:

data want;

   set have;

   by X;

   if first.X then d_new = 1;

   else d_new + 1;

run;

Then use D_NEW instead of D to transpose.

Good luck.

[Ksharp]

Do not use variable D , make a new variable for that purpose.

Not tested.

data transposed (drop=A B C D) ;

array AA {*} $ A1-A7;

array BB {*} $ B1-B7;

array CC {*} $ C1-C7;

n=0;

do until (last.X);

set rawdata;

by X;

  n+1;

  aa(n)= a;

  bb(n)=b;

  cc(n)=c;

end;

output;

run;