topic Re: Long-to-Wide handling duplicates using array processing in SAS Data Science

Long-to-Wide handling duplicates using array processing

mtsasuser — Wed, 03 Dec 2014 16:50:44 GMT

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;

Re: Long-to-Wide handling duplicates using array processing

M_Maldonado — Tue, 09 Dec 2014 14:51:08 GMT

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

Re: Long-to-Wide handling duplicates using array processing

Astounding — Tue, 09 Dec 2014 15:05:00 GMT

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.

Re: Long-to-Wide handling duplicates using array processing

Ksharp — Sun, 14 Dec 2014 09:39:20 GMT

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;

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

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

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