Date: Mon, 10 Nov 2003 14:23:18 -0800
Reply-To: Dorian Noel <d.noel@ISMACENTRE.RDG.AC.UK>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Dorian Noel <d.noel@ISMACENTRE.RDG.AC.UK>
Organization: http://groups.google.com
Subject: Re: Agggregating Paired Variables - Help Needed
Content-Type: text/plain; charset=ISO-8859-1
Hi,
Thanks for the many suggestions on my initial coding problem. I am
sorry but I need to post a new coding challenge. Initially, I wanted
to count the number of times paired participants traded with each
other for a given month/ day (see initial posting) and attched value
back to the original data set.
My new challenge is to represent this new (count) variable as a
proportion of the total aggregated trades of both participants on a
given day (see sample table below). For instance, on Jan. 2, 0-4820
traded a total of 11 (6 trades as 0-4280 and 5 trades as 4820-0).
Therefore, on Jan. 2, 0-4820 had a total of 11 trades ("paired
trades") bewteen them.
Now, on Jan. 2, member 0 traded a total of 13 (11 trades with 4820 and
2 trades with 1820), while member 4820 was partied to 15 trades (11
with member 0, one with member 1 and 3 with member 3757). Thus, total
aggregated trades for participant 0 and participnat 4820 was 28 on
Jan. 2. As a result, their paired trades as a proportion of their
total volume would be 0.39 ("prop_trade") on Jan. 2.
My objective is to compute this "prop_trade" figure and attach it to
the original data set as a variable. Fisrt, we need to compute the
number of times each member trade with each other on a given day..
Second, calculate their total trades for that day. Finally, express
their paired trades as a proportion of their aggregated trades for
that day.
I have spent a good part of a week trying various angles. I would,
therefore, greatly appreciate your suggestions.
Take care.
Dorian
buyer seller mon day PairedTrade Prop_trade
0 4820 Jan 2 11 0.39
0 4820 Jan 2 11 0.39
0 4820 Jan 2 11 0.39
0 4820 Jan 2 11 0.39
0 4820 Jan 2 11 0.39
0 4820 Jan 2 11 0.39
0 1820 Jan 2 2
0 1820 Jan 2 2
3757 4820 Jan 2 3
3757 4820 Jan 2 3
0 3757 Feb 17 2
0 3757 Feb 17 2
0 3757 Feb 18 2
0 3757 Feb 18 2
0 3757 Feb 19 2
0 3757 Feb 19 2
4820 0 Jan 2 11 0.39
4820 0 Jan 2 11 0.39
4820 0 Jan 2 11 0.39
4820 0 Jan 2 11 0.39
4820 0 Jan 2 11 0.39
4820 1 Jan 2 1
4820 3757 Jan 2 3
TERJEM@DSHS.WA.GOV (Terjeson, Mark) wrote in message news:<592E8923DB6EA348BE8E33FCAADEFFFC02606316@dshs-exch2>...
> 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
| 
