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;
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.
Find more tutorials on the SAS Users YouTube channel.