topic Re: transpose data from one variable in SAS Programming

transpose data from one variable

CharlesR — Fri, 15 Apr 2011 21:14:14 GMT

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

Re: transpose data from one variable

Reeza — Fri, 15 Apr 2011 21:55:20 GMT

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

Re: transpose data from one variable

CharlesR — Sat, 16 Apr 2011 02:17:28 GMT

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]

Re: transpose data from one variable

CharlesR — Sat, 16 Apr 2011 03:57:35 GMT

Ignore this; proc transpose is much easier

Re: transpose data from one variable

CharlesR — Sat, 16 Apr 2011 03:59:08 GMT

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]

Re: transpose data from one variable

Ksharp — Mon, 18 Apr 2011 04:11:13 GMT

[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