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
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;
try:
proc transpose data=have out=want(drop=_:) prefix=qtr;
var sale;
by brand notsorted;
run;
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
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.
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;
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.