Data steps to transpose a dataset

[stat_sas]

Hi All,

I've data set with 3 variables brand, qtr and sale. Please see below the data set and values assigned to variables

brand  qtr sale

master  1  25

master  2  26

master  3  28

master  4  30

dura    1  15

dura    2  17

dura    3  16

dura    4  20

royal   1  10

royal   2  10

royal   3  11

royal   4  9

I want to convert my data in the following format

brand   qtr1 qtr2  qtr3  qtr4

master  25   26    28    30

dura      15   17    16    20

royal     10   10     11    9

Please advise how can I convert the data in the desired format using data steps.

Thanks

[Linlin]

try:

proc transpose data=have out=want(drop=_:) prefix=qtr;

var sale;

by brand notsorted;

run;

[TomKari]

PROC TRANSPOSE will do it perfectly!

PROC TRANSPOSE DATA=have OUT=want(drop=qtr) PREFIX=qtr NAME=qtr;

var sale;

by brand notsorted;

run;

Tom

[Astounding]

If you're going to use a PROC TRANSPOSE solution, it would be safer to add:

id qtr;

That way, if your data is missing a row here or there the sales still get matched to the proper quarter.

If you truly need a DATA step solution, plenty of people here can provide it.  Just ask again.

[art297 · Accepted solution]

Here is how you could do it with a datastep:

data want;

  set have;

  by brand notsorted;

  array want_num(*) qtr1-qtr4 ;

  retain want_num;

  if first.brand then call missing(of want_num(*));

  want_num(qtr)=sale;

  drop qtr sale ;

  if last.brand then output;

run;

[stat_sas]

Thanks every body for suggesting a solution. I wanted to get it done using data steps. The code provided by Arthur is very helpful in this regard.

[art297]

You might want to be on the lookout for a paper that we will definitely present at the MWSUG meeting in September, and may be presenting at this year's SGF as well.  Keep an eye out for a paper called: A Better Way to Flip (Transpose) a SAS^® Dataset

KSharp, Astounding, Joe Whitehurst and I wrote it, and it presents and describes a SAS macro that uses a datastep to make tall files wide (i.e., transpose) but, when more than one variable has to be transposed, it can run more than 50 times faster than proc transpose.