Building models with SAS Enterprise Miner, SAS Factory Miner, SAS Visual Data Mining and Machine Learning or just with programming

Long-to-Wide handling duplicates using array processing

Reply
N/A
Posts: 1

Long-to-Wide handling duplicates using array processing

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?


XABCD
1qts1
2qts1
2rpts2
3qts1
3rpts2
3wts3
4qts1
4rpts2
4wts3
4tpts4
5qty1
5roty2
5wty3
5tuty4
5vty5
6qth1
6roth2
6spth3
6wpth4
6vth5
6tth6
7zts1
7wots2
7wpts2


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;

Super Contributor
Posts: 336

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

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

Super User
Posts: 5,079

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

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.

Super User
Posts: 9,671

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

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;

Ask a Question
Discussion stats
  • 3 replies
  • 293 views
  • 0 likes
  • 4 in conversation