BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
stat_sas
Ammonite | Level 13

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


1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

6 REPLIES 6
Linlin
Lapis Lazuli | Level 10

try:

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

var sale;

by brand notsorted;

run;

TomKari
Onyx | Level 15

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
PROC Star

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
Opal | Level 21

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
Ammonite | Level 13

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
Opal | Level 21

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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