Calculating words for a number

[moha ss]

Hello Everyone, Would that be possible to calculate the number of Participant and lead for each loan by the loan ID for example this is my table

loan_ID Firms Lender_role
1 A Participants
1 B Lead
1 C Participants

2 B Lead

I'd like to make my table look like this by using the loan id to calculate individually how many participants and lead in each loan


loan_ID Firms Lender_role number of Part number of lead
1 A Participants 2 1
1 B Lead 2 1
1 C Participants 2 1

2 B Lead 0 1

[moha ss]

this is a clear version using code of the table

this is my table

* 
clear
input byte loan_ID str1 Firms str12 Lender_role
1 "A" "Participants"
1 "B" "lead"        
1 "C" "Participants"
2 "B" "Lead"        
end

and this is what I want it to look like

* 
clear
input byte loan_ID str1 Firms str12 Lender_role byte(numberofPart numberoflead)
1 "A" "Participants" 2 1
1 "B" "lead"         2 1
1 "C" "Participants" 2 1
2 "B" "Lead"         0 1
end

[Nick Cox]

This works for your example, for which thanks. Even there I needed to cope with variant spellings.

clear
input byte loan_ID str1 Firms str12 Lender_role
1 "A" "Participants"
1 "B" "lead"        
1 "C" "Participants"
2 "B" "Lead"        
end

egen partic = total(lower(Lender_role) == "participants"), by(loan_ID)
egen lead = total(lower(Lender_role) == "lead"), by(loan_ID)

list, sepby(loan_ID)



     +------------------------------------------------+
     | loan_ID   Firms    Lender_role   partic   lead |
     |------------------------------------------------|
  1. |       1       A   Participants        2      1 |
  2. |       1       B           lead        2      1 |
  3. |       1       C   Participants        2      1 |
     |------------------------------------------------|
  4. |       2       B           Lead        0      1 |
     +------------------------------------------------+

I would use

tab Lender_role

to check carefully for variant spellings, starting with use of upper and lower case and watching out for misspellings and differing use of leading, trailing and embedded spaces.