DATA Step, Macro, Functions and more

Data steps to transpose a dataset

Accepted Solution Solved
Reply
Trusted Advisor
Posts: 1,204
Accepted Solution

Data steps to transpose a dataset

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



Accepted Solutions
Solution
‎02-27-2013 12:03 PM
PROC Star
Posts: 7,363

Re: Data steps to transpose a dataset

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;

View solution in original post


All Replies
Super Contributor
Posts: 1,636

Re: Data steps to transpose a dataset

try:

proc transpose data=have out=want(drop=_Smiley Happy prefix=qtr;

var sale;

by brand notsorted;

run;

PROC Star
Posts: 1,092

Re: Data steps to transpose a dataset

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

Super User
Posts: 5,082

Re: Data steps to transpose a dataset

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.

Solution
‎02-27-2013 12:03 PM
PROC Star
Posts: 7,363

Re: Data steps to transpose a dataset

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;

Trusted Advisor
Posts: 1,204

Re: Data steps to transpose a dataset

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.

PROC Star
Posts: 7,363

Re: Data steps to transpose a dataset

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 287 views
  • 2 likes
  • 5 in conversation