transpose data from one variable

[CharlesR]

hello,
I have a data set which looks like this:
[pre]
z BT BBV DP Bat Res field
23 5 1 N R Out 0
23 5 1 N R Out 5
23 5 1 Y L Out 0
23 5 1 Y L Out 5
23 5 1 Y L Out 9
[/pre]
The first two line of data show one scenario with 2 different fielders, 0 & 5. The next 3 lines show a different scenario with 3 fielders, 0, 5 & 9. I then want it to look like this:
[pre]
z BT BBV DP Bat Res field1 field2 field3
23 5 1 N R Out 0 5
23 5 1 Y L Out 0 5 9
[/pre]
What do i need to do to make this work?

Message was edited by: CharlesR Message was edited by: CharlesR

[Reeza]

How do you identify the 'beginning' or 'end' of a scenario. I'm guessing that Bat is batter Left or Right, so they could be the same back to back and harder to identify where one scenario starts and one ends?

See this page for sample code to help with changing the stucture of your dataset.

http://www.ats.ucla.edu/stat/sas/modules/longtowide_data.htm

[CharlesR]

So I tried to follow the code which you referenced with your link, and it isn't working. Here's the code I've got:
[pre]
proc sort data = def_means_byposition;
by zone BallInPlayType BattedBallVelocity DblPlayPosn BatHandedness fieldingbypos;
run;

DATA ResponsibleFielders;
SET def_means_byposition;
by zone BallInPlayType BattedBallVelocity DblPlayPosn BatHandednessPlayRes_forPerc fieldingbypos;

KEEP zone BallInPlayType BattedBallVelocity DblPlayPosn BatHandedness PlayRes_forPerc RespFielder1-RespFielder4;
RETAIN RespFielder1-RespFielder4;

ARRAY aRespFielder(1:4) RespFielder1 - RespFielder4 ;

IF first.BatHandedness THEN DO;
DO i = 1 to 4 ;
aRespFielder( i ) = 0;
end;
end;

aRespFielder(fieldingbypos) = Count;
IF last.BatHandedness THEN OUTPUT ;
RUN;
[/pre]
And here's the error i get:
[pre] ERROR: Array subscript out of range at line 1168 column 3.[/pre]

Any idea what i've done wrong?

Here's the code which was referenced: [pre]
PROC SORT DATA=long3 OUT=longsrt3 ;
BY famid birth ;
RUN ;

DATA wide3 ;
SET longsrt3 ;
BY famid birth ;

KEEP famid ht1-ht2 ;
RETAIN ht1-ht2 ;

ARRAY aht(1:2) ht1-ht2 ;

IF first.birth THEN
DO;
DO i = 1 to 2 ;
aht( i ) = 0 ;
END;
END;

aht( age ) = ht ;

IF last.birth THEN OUTPUT ;

RUN; [/pre]

[CharlesR]

Ignore this; proc transpose is much easier

[CharlesR]

proc transpose is the best way to work this. Here's the code i used.
[pre]
proc transpose data = def_means_byposition out = ResponsibleFielders (drop = _Name_) prefix = fielder;
by zone BallInPlayType BattedBallVelocity DblPlayPosn BatHandedness PlayRes_forPerc;
var fieldingbypos;
run;
[/pre]

[Ksharp]

[pre]
data temp;
input z BT BBV DP $ Bat $ Res $ field ;
cards;
23 5 1 N R Hit 0
23 5 1 N R Out 5
23 5 1 Y L Hit 0
23 5 1 Y L Out 5
23 5 1 Y L Out 9
;
run;
data temp;
set temp;
if res = 'Hit' then count+1;
run;

data want;
set temp;
by count ;
length fieldres $ 200;
retain fieldres num;
nobs+1;
if first.count then do;
call missing (fieldres);
num=nobs;
end;
fieldres=catx(' ',fieldres,field);
if last.count then do;
set temp point=num;
output;
end;
drop field nobs count;
run;
[/pre]


Ksharp