BookmarkSubscribeRSS Feed
kyoo
Calcite | Level 5

vHi,

I'd like to change the dataset

From

Acctmail dateOrder
1111-Jan1
1111-Feb2
1111-Mar3
2221-Jan1
2221-Apr2

To

AcctOrder 1Order 2Order 3
1111-Jan1-Feb1-Mar
2221-Jan1-Apr

How can I do this in SAS?

Thanks,

Kwang

3 REPLIES 3
zana
Calcite | Level 5

You can do it with the following program.

data kyoo;

input Acct maildate$ Order;

cards;

111 1-Jan 1

111 1-Feb 2

111 1-Mar 3

222 1-Jan 1

222 1-Apr 2

;

proc transpose data=b;

var maildate;

by acct;

run;

ballardw
Super User

If the order is always sequential it isn't even needed.

 

data have;

input Acct maildate $ Order ;

datalines;

111 1-Jan 1

111 1-Feb 2

111 1-Mar 3

222 1-Jan 1

222 1-Apr 2

run;

/* assumes data is sorted by acct and order number*/

proc transpose data=have out=want (drop=_name_)

prefix=order

;

by acct;

var maildate;

run;

SKK
Calcite | Level 5 SKK
Calcite | Level 5

Hi Kyoo,

Proc transpose would work fine for your data. But what if the data is like this...

111 1-Jan 1

111 1-Feb 3

111 1-Mar 4

222 1-Jan 1

222 1-Apr 3

Order2 will not be included in your output. For this u have to go with data step as given below,

DATA HAVE;
INPUT Acct mail_date $ Order;
DATALINES;
111 1-Jan 1
111 1-Feb 3
111 1-Mar 4
222 1-Jan 1
222 1-Apr 3
;
RUN;

*Selecting the maximum of order;

PROC SQL NOPRINT;
SELECT MAX(ORDER) INTO :M_ORD FROM HAVE ;
QUIT;

PROC SORT DATA=HAVE;
BY ACCT;
RUN;

DATA WANT(DROP=ORD MAIL_DATE I);
SET HAVE (RENAME=(ORDER=ORD));
ARRAY ORDER(&M_ORD) $  ;
BY ACCT;
RETAIN ORDER: ;
IF FIRST.ACCT THEN
DO I = 1 TO &M_ORD;
ORDER(I)=" ";
END;
ORDER(ORD)= MAIL_DATE;
IF LAST.ACCT;
RUN;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1041 views
  • 0 likes
  • 4 in conversation