Date:         Tue, 4 Nov 2003 13:53:34 -0800
Reply-To:     "Terjeson, Mark" <TERJEM@DSHS.WA.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Terjeson, Mark" <TERJEM@DSHS.WA.GOV>
Subject:      Re: Agggregating Paried Variables - Help Needed
Comments: To: Dorian Noel <d.noel@ISMACENTRE.RDG.AC.UK>
Content-Type: text/plain; charset=iso-8859-1

Hi Dorian,

Here is one approach.......


data sample;
    input buyer seller mon $ day;
cards;
0 4820 Jan 2
0 4820 Jan 2
0 4820 Jan 2
0 4820 Jan 2
0 4820 Jan 2
0 4820 Jan 2
0 1820 Jan 2
0 1820 Jan 3
0 1820 Jan 3
0 3757 Feb 17
0 3757 Feb 17
0 3757 Feb 17
0 3757 Feb 18
0 3757 Feb 18
0 3757 Feb 19
0 3757 Feb 19
4820 0 Jan 6
4820 0 Jan 6
4820 0 Jan 6
4820 0 Jan 6
4820 0 Jan 6
4820 0 Jan 6
4820 0 Jan 6
;
run;



    * interactions during month ;
proc sql;
    create table temp1 as
    select
         distinct
             min(buyer,seller) as user1,
             max(buyer,seller) as user2,
             mon,
             count(mon) as MonthCount
    from
             sample
    group by
             min(buyer,seller),
             max(buyer,seller),
             mon
    ;
quit;


    * interactions during day ;
proc sql;
    create table temp2 as
    select
         distinct
             min(buyer,seller) as user1,
             max(buyer,seller) as user2,
             mon,
             day,
             count(day) as DayCount
    from
             sample
    group by
             min(buyer,seller),
             max(buyer,seller),
             mon,
             day
    ;
quit;



    * to glue MonthCount back to original file ;
proc sql;
    create table sample as
    select
           a.*,
           b.MonthCount
    from
           sample as a
        left join
           temp1 as b
        on (a.buyer eq b.user1 and a.seller eq b.user2)
        or (a.buyer eq b.user2 and a.seller eq b.user1)
    ;
quit;



Hope this is helpful,
Mark Terjeson
Reporting, Analysis, and Procurement Section
Information Services Division
Department of Social and Health Services
State of Washington
mailto:terjem@dshs.wa.gov





-----Original Message-----
From: Dorian Noel [mailto:d.noel@ISMACENTRE.RDG.AC.UK]
Sent: Tuesday, November 04, 2003 12:45 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Agggregating Paried Variables - Help Needed


Hi;

I am attempting to create a variable that represents the number of
time two participants to a transaction trade with each other for a
given month. I have attached a smaple data set for your perusal.

I should stress that a given participant, encrypted buyer/ seller
fields, can be either a buyer or seller to an opposing counter-party
on all trades.  For instance, 0 is a buyer to 4820 on some trades but
on others, he is a seller to 4820.

My aim, therefore, is to compute the number of time each paired
participants interact with each other for a given month/ day (week, if
possible) and return the the value to the data set as a variable (see
inserted count field for illustration).

Hope you have some useful suggestions.

Take care.

Dorian

buyer   seller  mon     day     Count
0       4820    Jan     2       13
0       4820    Jan     2       13
0       4820    Jan     2       13
0       4820    Jan     2       13
0       4820    Jan     2       13
0       4820    Jan     2       13
0       1820    Jan     2
0       1820    Jan     3
0       1820    Jan     3
0       3757    Feb     17
0       3757    Feb     17
0       3757    Feb     17
0       3757    Feb     18
0       3757    Feb     18
0       3757    Feb     19
0       3757    Feb     19
4820    0       Jan     6       13
4820    0       Jan     6       13
4820    0       Jan     6       13
4820    0       Jan     6       13
4820    0       Jan     6       13
4820    0       Jan     6       13
4820    0       Jan     6       13