Collapse command

[Kashif Khan]

Dear all,

I have a dataset that looks like this. People have taken multiple loans for multiple reasons.

ID	Gender	Reasons for taking loans
		Loan1	Loan2	Loan3	Loan4
1	Female	1	1	1	3
2	Male	1	2	1	2
3	Female	3	1	2	.
4	Male	2	3	.	.

I need to see what were the reasons for taking the loans (total of three categories). I want to produce something like this

Gender	Count of each reason			Total
	Count 1	Count 2	Count 3
Male	2	3	1	6
Female	4	1	2	7

I tried using the collapse and count command on Stata 14.2 but could not succeed. Do I need to restructure my data, or will a better collapse coding help?

Thanks!

[Tarun Choudhary]

Providing sample data using dataex is helpful. Collapse is not the right approach for what you want to achieve. You will need to convert the data from wide to long format and then tabulate to get the desired output. The following code should work.

reshape long reason, i(ID) j(dummy) 
tab Gender reason

[Kashif Khan]

Providing sample data using dataex is helpful. Collapse is not the right approach for what you want to achieve. You will need to convert the data from wide to long format and then tabulate to get the desired output. The following code should work.

reshape long reason, i(ID) j(dummy)
tab Gender reason

Here is the full data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str4 BeneficiaryID byte(Gender Reasonfortakingloan1 Reasonfortakingloan2 Reasonfortakingloan3 Reasonfortakingloan4 Reasonfortakingloan5)
"4101" 1 1 . . . .
"4102" 1 1 . . . .
"4103" 1 1 1 . . .
"4104" 1 1 . . . .
"4105" 1 2 . . . .
"4106" 1 2 1 1 1 .
"4107" 1 1 . . . .
"4108" 1 2 1 1 . .
"4109" 1 2 1 . . .
"4110" 1 2 . . . .
"4111" 1 1 1 . . .
"4112" 1 1 . . . .
"4113" 1 1 1 . . .
"4214" 2 1 . . . .
"4215" 2 1 1 . . .
"4216" 2 1 1 1 1 .
"4217" 2 1 . . . .
"4218" 2 1 . . . .
"4219" 2 2 1 1 . .
"4220" 2 3 . . . .
"4121" 1 2 . . . .
"4122" 1 2 1 . . .
"4123" 1 2 . . . .
"4124" 1 2 . . . .
"4125" 1 1 1 . . .
"4126" 1 2 . . . .
"4228" 2 2 . . . .
"4229" 2 1 . . . .
"4230" 2 2 . . . .
"4131" 1 2 . . . .
"4132" 1 2 1 . . .
"4133" 1 2 . . . .
"4134" 1 2 1 . . .
"4135" 1 2 1 . . .
"4136" 1 2 1 . . .
"4137" 1 2 . . . .
"4138" 1 2 . . . .
"4139" 1 1 . . . .
"4140" 1 1 . . . .
"3141" 1 1 . . . .
"3142" 1 2 2 2 . .
"3143" 1 2 . . . .
"3144" 1 2 1 1 1 .
"3145" 1 1 . . . .
"3146" 1 1 2 . . .
"3147" 1 3 1 . . .
"3148" 1 1 1 . . .
"3149" 1 1 1 1 . .
"3150" 1 2 1 . . .
"3151" 1 1 . . . .
"3152" 1 1 . . . .
"3153" 1 3 . . . .
"3254" 2 1 2 . . .
"3255" 2 2 2 2 2 2
"3256" 2 1 . . . .
"3257" 2 1 1 1 . .
"3258" 2 1 . . . .
"3159" 1 1 1 . . .
"3160" 1 2 . . . .
"2261" 2 2 1 1 . .
"2262" 2 3 3 3 . .
"2163" 1 2 . . . .
"2164" 1 1 2 . . .
"2165" 1 2 . . . .
"2166" 1 1 . . . .
"2267" 2 1 1 1 . .
"2168" 1 1 . . . .
"2169" 1 1 . . . .
"2170" 1 2 . . . .
"1271" 2 2 2 . . .
"1172" 1 2 1 1 . .
"1173" 1 1 . . . .
"1174" 1 1 . . . .
"1275" 2 1 1 1 2 .
"1176" 1 2 . . . .
"1177" 1 2 . . . .
"1178" 1 1 . . . .
"1179" 1 1 . . . .
"1280" 2 1 . . . .
end
label values Gender gen
label def gen 1 "Male", modify
label def gen 2 "Female", modify
label values Reasonfortakingloan1 loanpurpose1
label def loanpurpose1 1 "Expand existing business", modify
label def loanpurpose1 2 "Set up a new business", modify
label def loanpurpose1 3 "Consumption purpose", modify
label values Reasonfortakingloan2 loanpurpose2
label def loanpurpose2 1 "Expand existing business", modify
label def loanpurpose2 2 "Set up a new business", modify
label def loanpurpose2 3 "Consumption purpose", modify
label values Reasonfortakingloan3 loanpurpose3
label def loanpurpose3 1 "Expand existing business", modify
label def loanpurpose3 2 "Set up a new business", modify
label def loanpurpose3 3 "Consumption purpose", modify
label values Reasonfortakingloan4 loanpurpose4
label def loanpurpose4 1 "Expand existing business", modify
label def loanpurpose4 2 "Set up a new business", modify