DATA Step, Macro, Functions and more

transpose data from one variable

Reply
Contributor
Posts: 58

transpose data from one variable

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
Super User
Posts: 19,815

Re: transpose data from one variable

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
Contributor
Posts: 58

Re: transpose data from one variable

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]
Contributor
Posts: 58

Re: transpose data from one variable

Ignore this; proc transpose is much easier
Contributor
Posts: 58

Re: transpose data from one variable

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]
Super User
Posts: 10,035

Re: transpose data from one variable

[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
Ask a Question
Discussion stats
  • 5 replies
  • 216 views
  • 0 likes
  • 3 in conversation