Date:         Sat, 29 Apr 2006 10:10:18 -0400
Reply-To:     Ya Huang <ya.huang@AMYLIN.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ya Huang <ya.huang@AMYLIN.COM>
Subject:      Re: SQL or Data step question

Data step is more straightforward:


data xx;
input Device  Type  Seq1 $ Seq2 $;
cards;
90001   1    A    5
90001   1    A    6
90001   1    B    4
90001   1    B    3
90001   1    B    2
90003   1    A    5
90003   1    B    4
90003   1    B    3
;

proc sort;
by device seq1 seq2;
run;

data xx;
 set xx;
length cat $100;
retain cat;
by device seq1 seq2;
if first.device then cat='';
if first.seq2 then cat=compress(cat||seq1||seq2);
if last.device;
if cat='A5A6B2B3B4' then cat1=99; else cat1=88;
run;

proc print;
run;


Device    Type    Seq1    Seq2    cat           cat1

 90001      1      B       4      A5A6B2B3B4     99
 90003      1      B       4      A5B3B4         88


HTH

Ya

On Sat, 29 Apr 2006 00:21:27 -0700, pagabas <pagabas@YAHOO.COM> wrote:

>Consider the rows for a given extract:
>
>DeviceNumber         Type              Sequence1                  Sequence2
>90001                    1
A                                    5
>90001                    1
A                                    6
>90001                    1
B                                    4
>90001                    1
B                                    3
>90001                    1
B                                    2
>
>
>The combination of Sequence1 and Sequence2 for DeviceNumber  will be
>used to categorize
>each device.  The possible combinations are:  A-5, A-6,  B-4,  B-3,
>B-2.  Sometimes, you will not have all these combinations for each
>deviceNumber.
>
>So, if DeviceNumber 90003 has the combinations A-5, B-3 and B-4  I want
>to write out
>90003 A5B3B4  88      (The 88 is a category number that I am
>assigning).
>
>The real problem is to write out a single row for each DeviceNumber.
>Any idea on joins and
>Case statements?
>Thanks
>Pagabas