topic Re: Data steps to transpose a dataset in SAS Programming

Data steps to transpose a dataset

stat_sas — Wed, 27 Feb 2013 15:26:29 GMT

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

Re: Data steps to transpose a dataset

Linlin — Wed, 27 Feb 2013 16:23:22 GMT

try:

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

var sale;

by brand notsorted;

run;

Re: Data steps to transpose a dataset

TomKari — Wed, 27 Feb 2013 16:24:23 GMT

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

Re: Data steps to transpose a dataset

Astounding — Wed, 27 Feb 2013 16:36:24 GMT

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.

Re: Data steps to transpose a dataset

art297 — Wed, 27 Feb 2013 17:03:36 GMT

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;

Re: Data steps to transpose a dataset

stat_sas — Thu, 28 Feb 2013 03:37:02 GMT

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.

Re: Data steps to transpose a dataset

art297 — Thu, 28 Feb 2013 03:58:28 GMT

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.