This is one of those problems which is easier to solve if broken down into smaller steps.

1. Compute the aggregate counts for each party for each day.

create table agg as select *, count(*) as agg from (select buyer as party, mon, day from sample outer union corresponding select seller as party, mon, day from sample) group by party, mon, day;

2. Since the direction of the trades (who buys and who sells) does not matter, standardize the dataset by identifying the party with the lower ID as A and the other as B.

create table minAmaxB as select monotonic() as origseq, min(buyer,seller) as partyA, max(buyer,seller) as partyB, * from sample;

The undocumented MONOTONIC function allows capture of the original sequence. It is only needed if it's necessary to preserve that sequence in the end result.

3. Combine the two intermediate results.

create table all as select minAmaxB.*, aggA.agg as aggA, aggB.agg as aggB from minAmaxB, agg as aggA, agg as aggB where minAmaxB.partyA=aggA.party and minAmaxB.partyB=aggB.party and minAmaxB.mon =aggA.mon and minAmaxB.mon =aggB.mon and minAmaxB.day =aggA.day and minAmaxB.day =aggB.day;

4. Derive the two new columns.

create table result as select buyer, seller, mon, day, count(*) as PairedTrade, calculated PairedTrade / (aggA + aggB) format=5.2 as Prop_trade from all group by partyA, partyB, mon, day order by origseq;

This solution double counts the paired trades in the denominator, thus conforming to the example. To avoid that, subtract the PairedTrade count in the denominator subexpression.

On Mon, 10 Nov 2003 14:23:18 -0800, Dorian Noel <d.noel@ISMACENTRE.RDG.AC.UK> wrote:

>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