On 30 oct, 10:59, ChrisG <chris.godlew...@gmail.com> wrote: > On 28 oct, 22:03, HERMA...@WESTAT.COM (Sigurd Hermansen) wrote: > > > > > Why not simply compute the numbers directly? > > > data test; > > input deal lender: $char2. borrower: $char2. year; > > cards; > > 1 l1 b1 2000 > > 1 l2 b1 2000 > > 1 l3 b1 2000 > > 2 l4 b1 2000 > > 2 l1 b1 2000 > > 3 l2 b2 2000 > > 3 l4 b2 2000 > > 3 l5 b2 2000 > > 4 l2 b3 2002 > > 4 l6 b3 2002 > > 4 l3 b3 2002 > > ; > > run; > > /* > > 1. evaluate the closeness between a lender i and a borrower j with some number > > telling me that lender i was in N deals for borrower j and this tells me how > > much lender i and borrower j know each other > > */ > > > proc sql; > > select lender,borrower,count(*) as n > > from test > > group by lender,borrower > > ; > > quit; > > > /* > > 2. evaluate the closeness between a lender k and a lender l with some number > > telling me that these 2 guys where together in M deals > > */ > > proc sql; > > select t1.lender as lenderk,t2.lender as lenderL,count(*) as m > > from test as t1,test as t2 > > where t1.deal=t2.deal and t1.lender < t2.lender > > group by lenderk,lenderL > > ; > > quit; > > > S > > > -----Original Message----- > > From: SAS(r) Discussion [mailto:SA...@LISTSERV.UGA.EDU] On Behalf OfChrisG > > Sent: Tuesday, October 28, 2008 4:21 PM > > To: SA...@LISTSERV.UGA.EDU > > Subject: creating an index for "repeated actions" > > > Hi folks > > > Hopefully some of you already had to deal with such an issue in their lives so I really count on you ! > > > Here is my story > > > I have a sample which looks like this : > > > deal lender borrower year > > 1 l1 b1 2000 > > 1 l2 b1 2000 > > 1 l3 b1 2000 > > 2 l4 b1 2000 > > 2 l1 b1 2000 > > 3 l2 b2 2000 > > 3 l4 b2 2000 > > 3 l5 b2 2000 > > 4 l2 b3 2002 > > 4 l6 b3 2002 > > 4 l3 b3 > > 2002 > > ... > > > and so on > > basically i have some deals where some lenders interact with themselves across deals (or years) and these lenders also interact with borrowers across deals (or years) (for those who know this is a story about syndicated lending ...) what i am looking for is to compute 2 types of index or measures that would allow me to : > > > 1. evaluate the closeness between a lender i and a borrower j with some number telling me that lender i was in N deals for borrower j and this tells me how much lender i and borrower j know each other > > > 2. evaluate the closeness between a lender k and a lender l with some number telling me that these 2 guys where together in M deals > > > I hope that i was clear enough ... > > > I would appreciate very much any kind of help on that issue ! > > > Best > > Cheers > > CG > > Hi again > > i have some problems with the second proc sql on my data ... > > i get a statement in log : NO ROWS WERE SELECTED ... > > my data looks like this : > > lender_name id_lender borrower_name year > borrower_id2 deal_id2 > JP Morgan & Co 2088 Adisseo France SAS > 2005 1 1 > BNP Paribas 247 Aeroports de Paris > 1998 2 2 > Le Credit Lyonnais SA 2270 Alain Afflelou SA > 2006 3 3 > Bank of Ireland Group 685 Alma Consulting Group > 2006 4 4 > BNP Paribas SA 245 Aluminium Pechiney > 2004 5 5 > CIC Europeene International 968 Aluminium Pechiney > 2002 6 6 > ... > etc > > i can't figure out why it doesn't work ! > > thanks in advance for any help > cheers > CG

Ok so i received a mail (see below) :

Hi,

I have been thinking on a datastep solution. I found the SQL solution gives an output that is different from what I think.

Can you give some desirable indices like number of times that Li to Lj occurs given a DEAL based on the example data? That would help me to suggest probably a speedy solution. You may post it to SAS-L and not to me direct as it would invite other SAS-Lers to strike a good solution.

Thanks.

Muthia Kachirayan

now i am not sure about the question you ask ... actually i am looking for SAS to do the job for me i.e. give me such indices of repeated action by deal i guess a good starting point would be to know how many times bank 1 and bank 2 were together in any deal ... for instance, if we refer to the first example, l2 and l3 "met together" in 2 deals (number 1 and 4) so the indice would be equal to 2 in that case and that would be a good starting point for me

hope that helped cheers CG