topic aggregate values in one row per primary key in SAS Programming

aggregate values in one row per primary key

kyoo — Fri, 18 Apr 2014 17:33:57 GMT

vHi,

I'd like to change the dataset

From

Acct	mail date	Order
111	1-Jan	1
111	1-Feb	2
111	1-Mar	3
222	1-Jan	1
222	1-Apr	2

Acct	Order 1	Order 2	Order 3
111	1-Jan	1-Feb	1-Mar
222	1-Jan	1-Apr

How can I do this in SAS?

Thanks,

Kwang

Re: aggregate values in one row per primary key

zana — Fri, 18 Apr 2014 17:48:38 GMT

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;

Re: aggregate values in one row per primary key

ballardw — Fri, 18 Apr 2014 17:52:39 GMT

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;

Re: aggregate values in one row per primary key

SKK — Mon, 21 Apr 2014 05:23:15 GMT

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;