Date:         Tue, 10 Dec 2002 13:27:22 -0800
Reply-To:     "Huang, Ya" <yhuang@AMYLIN.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Huang, Ya" <yhuang@AMYLIN.COM>
Subject:      Re: Help in Manipulating Dataset.
Comments: To: John Hewitt <john_hewitt2003@YAHOO.COM>
Content-Type: text/plain; charset="iso-8859-1"

John,

Here is a brute force solution, the final merge
step can be generated by some macro using sql "select into",
or call execute etc. to make it more automatic.

data xx;
input Wk  Categ $ Grs  Net;
cards;
1    AA1  380  280
1    BB1  260  200
1    AA2  320  240
1    BB2  340  560
2    BB1  240  180
2    AA2  220  140
3    BB1  400  320
3    AA2  350  260
;

data xx;
 set xx;
by wk;
if first.wk then rk=0;
rk+1;
run;

proc sql;
create table xx as
select *, max(gn) as maxgn
from (select *,count(*) as gn
      from xx
      group by wk)
order by wk,rk
;

data xx (keep=Wk Categ Grs Net rk);
 set xx;
if gn < maxgn then rk=rk+maxgn-gn;
run;

data xx (drop=rk);
 merge xx(where=(wk=1))
       xx(rename=(wk=wk1 categ=categ1 grs=grs1 net=net1) where=(wk1=2))
         xx(rename=(wk=wk2 categ=categ2 grs=grs2 net=net2) where=(wk2=3));
 by rk;
run;

options nocenter;
proc print noobs;
run;

--------------

Wk Categ Grs Net wk1 categ1 grs1 net1 wk2 categ2 grs2 net2

 1  AA1  380 280  .            .    .  .            .    .
 1  BB1  260 200  2   BB1    240  180  3   BB1    400  320
 1  AA2  320 240  2   AA2    220  140  3   AA2    350  260

Kind regards,

Ya Huang

-----Original Message-----
From: John Hewitt [mailto:john_hewitt2003@YAHOO.COM]
Sent: Tuesday, December 10, 2002 11:50 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Help in Manipulating Dataset.


Dear All:

Can anyone kindly suggest how to convert a dataset in
the following format:

Wk  Categ Grs  Net
1    AA1  380  280
1    BB1  260  200
1    AA2  320  240
2    BB1  240  180
2    AA2  220  140
3    BB1  400  320
3    AA2  350  260

to a dataset in the following format

Wk Categ Grs   Net  Wk Categ Grs Net Wk Categ Grs  Net
1    AA1  380  280
1    BB1  260  200  2  BB1  240  180 3    BB1  400
320
1    AA2  320  240  2  AA2  220  140 3    AA2  350
260


Thanks in advance for your suggestions.

John


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com