```Date: Mon, 10 Nov 2003 14:23:18 -0800 Reply-To: Dorian Noel Sender: "SAS(r) Discussion" From: Dorian Noel 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 ```

Back to: Top of message | Previous page | Main SAS-L page