aggregate values in one row per primary key

Reply
N/A
Posts: 1

aggregate values in one row per primary key

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

Frequent Contributor
Posts: 81

Re: aggregate values in one row per primary key

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;

Super User
Posts: 11,343

Re: aggregate values in one row per primary key

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;

Contributor SKK
Contributor
Posts: 35

Re: aggregate values in one row per primary key

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;

Ask a Question
Discussion stats
  • 3 replies
  • 214 views
  • 0 likes
  • 4 in conversation