/********************************************************************************************************************************
I. This section creates summary statistics regarding the characteristics of the firms*******************************************/

use FFsurvey_finaldata
log using tabulations, replace

/*a) SIZE OF FIRMS- According to data from Serbian bank-*/
tab Size
sort Size
by Size: sum num_employees2012, detail
by Size:sum num_employees2012
sum num_employees2012, detail


/*Size of firms according to questionnaire*/
tab a4
/*results are similar, 90.5% of the firms have less than 50 employees, while 9.5% have more than 50.*/

/*Do they provide transport services?*/
tab a6 Size,column nofreq


/*b)QD2: Number of import declarations*/
sum d2, detail

/* recoding d2- some mistakes, code 888997 is equivalent a DK*/
gen shipments_number=d2
recode shipments_number 0=888997 if Serial==2090 /*this answer can't be zero*/
recode shipments_number 1600=888997 if Serial==5016 /*they answered # of jobs not shipments*/
sum shipments_number if shipments_number!=888997,detail

format shipments_number %9.0fc
tabstat shipments_number if shipments_number!=888997,by(Size) stat( n mean sd q) format
tabstat shipments_number if shipments_number!=888997, stat( n mean sd q) format

/*c) QD3-Share of business that involves largest client*/
gen client_diversification=d3
sum client_diversification if client_diversification!=888997,detail /*7 firms have DK*/
format client_diversification %9.0fc
tabstat client_diversification if client_diversification!=888997,by(Size) stat(mean sd q N)format
tabstat client_diversification if client_diversification!=888997, stat(mean sd q)format

/*d) QD24a-value of consignments*/

sum d24a if d24a!=888997,detail 
format d24 %9.0fc
tabstat d24 if d24a!=888997, by (Size) stat(mean sd q) format

/*fixing values from interviewer #2376, noted quantitites in thousands*/
gen shipment_value=d24a
recode shipment_value 30=30000 if intid==2376
recode shipment_value 10=10000 if intid==2376
recode shipment_value 20=20000 if intid==2376
recode shipment_value 15=15000 if intid==2376
recode shipment_value 20=20000 if intid==2376
recode shipment_value 7=7000 if intid==2376

/*For small firms*/
sum shipment_value if Size==1 & shipment_value!=888997 ,detail
/*oUTLIERS ARE THOSE LARGER THAN 47,500 (USING iqr RULE)*/
replace shipment_value=888997 if (shipment_value >=47500) & (Size==1) /*21 OUTLIERS*/

/*Outliers>=67500) for large firms*/
sum shipment_value if Size==2 & shipment_value!=888997 ,detail
replace shipment_value=888997 if (shipment_value >=67500) & (Size==2) /*2 outliers*/
format shipment_value %9.0fc

tabstat shipment_value if shipment_value!=888997,by(Size) stat( n mean sd q) format
tabstat shipment_value if shipment_value!=888997, stat( n mean sd q) format


/*E)Type of products Imported: QD4*/
gen agri_products=1 if (d4_1==1|d4_1==2)| (d4_2==1|d4_2==2)| (d4_3==1|d4_3==2)| (d4_4==1|d4_4==2)/*this includes vegetable and animal products*/
recode agri_products .=0
gen raw_materials=1 if (d4_1==4|d4_1==5|d4_1==6|d4_1==7|d4_1==8|d4_1==10|d4_1==11|d4_1==12)| (d4_2==4|d4_2==5|d4_2==6|d4_2==7|d4_2==8|d4_2==10|d4_2==11|d4_2==12)| (d4_3==4|d4_3==5|d4_3==6|d4_3==7|d4_3==8|d4_3==10|d4_3==11|d4_3==12)| (d4_4==4|d4_4==5|d4_4==6|d4_4==7|d4_4==8|d4_4==10|d4_4==11|d4_4==12)
recode raw_materials .=0 /*raw includes, mineral, chemicals, plaistics wood, textiles, glass, metals*/
gen food_products=1 if (d4_1==3|d4_2==3| d4_3==3|d4_4==3)
recode food_products .=0 /*includes beverages spirits, vinegar, tobacco*/
gen manufactures=1 if (d4_1==13|d4_1==14|d4_1==15|d4_1==16|d4_1==17)|(d4_2==13|d4_2==14|d4_2==15|d4_2==16|d4_2==17)|(d4_3==13|d4_3==14|d4_3==15|d4_3==16|d4_3==17)|(d4_4==13|d4_4==14|d4_4==15|d4_4==16|d4_4==17)
recode manufactures .=0 /*includes 84-95 hs codes*/
gen other_products=1 if (d4_1==18|d4_2==18| d4_3==18|d4_4==18) /*HS97*/
recode other_products .=0

tabstat agri_products if agri_products==1, stats(n)
tabstat food_products if food_products==1,stats(n)
tabstat raw_materials if raw_materials==1,stats(n)
tabstat manufactures if manufactures==1,stats(n)
tabstat other_products if other_products==1,stats(n)

/*F) Country of origin: Qd5*/
label define d5_6x_1 8 "Asia", modify
label define d5_6x_1 11 "Others", modify

/*this generates categories/brackets for each source*/
gen EU=d5_1
recode EU 0/100=1 if d5_1<=25
recode EU 0/100=2 if d5_1>25 & d5_1<=50
recode EU 0/100=3 if d5_1>50 & d5_1<=75
recode EU 0/100=4 if d5_1>75 & d5_1<=100
label define EU 1 "0-25" 2 "26-50" 3 "51-75" 4"76-100"
label values EU EU

gen CEFTA=d5_2
recode CEFTA 0/100=1 if d5_2<=25
recode CEFTA 0/100=2 if d5_2>25 & d5_2<=50
recode CEFTA 0/100=3 if d5_2>50 & d5_2<=75
recode CEFTA 0/100=4 if d5_2>75 & d5_2<=100
label define CEFTA 1 "0-25" 2 "26-50" 3 "51-75" 4"76-100"
label values CEFTA CEFTA

gen Europe_other=d5_3
recode Europe_other 0/100=1 if d5_3<=25
recode Europe_other 0/100=2 if d5_3>25 & d5_3<=50
recode Europe_other 0/100=3 if d5_3>50 & d5_3<=75
recode Europe_other 0/100=4 if d5_3>75 & d5_3<=100
label define Europe_other 1 "0-25" 2 "26-50" 3 "51-75" 4"76-100"
label values Europe_other Europe_other

gen China=d5_4
recode China 0/100=1 if d5_4<=25
recode China 0/100=2 if d5_4>25 & d5_4<=50
recode China 0/100=3 if d5_4>50 & d5_4<=75
recode China 0/100=4 if d5_4>75 & d5_4<=100
label define China 1 "0-25" 2 "26-50" 3 "51-75" 4"76-100"
label values China China

 tab EU
 tab CEFTA
 tab Europe_other
 tab China
 tab d5_6x_1
 
/*Average share of consignements according to size of firms*/
rename d5_1 European_Union
rename d5_2 CEFTA_
rename d5_3 Elsewhere_in_Europe_
rename d5_4 North_America
rename d5_5 China_
rename d5_6 Other
format European_Union CEFTA_ Elsewhere_in_Europe_ North_America China_ Other %9.0fc
tabstat European_Union CEFTA_ Elsewhere_in_Europe_ North_America China_ Other , by (Size) format
tabstat European_Union CEFTA_ Elsewhere_in_Europe_ North_America China_ Other , format stats(mean count)
 

 /******************************************II. HOW FREIGHTFORWARDERS OPERATE********************************/
 /*a) Consolidation of imports (Qe1-e2)*/
gen consolidation=e1
label define consolidation 1 "Always" 2 "Frequently" 3 "Sometimes" 4 "Rarely" 5 "Never"
label values consolidation consolidation
tab consolidation if consolidation !=888997
tab consolidation Size if consolidation !=888997,column nofreq
tab consolidation  if consolidation !=888997

tab e1a
gen consolidation_type=e1a
label define consolidation_type 1 "Same products, different clients" 2 "Different products, same clients" 3 "Different products and clients"
label values consolidation_type consolidation_type
tab consolidation_type
tab consolidation_type Size, column nofreq

tab e2 if e2!=888997
tab e2 Size if e2!=888997, column nofreq
tab e2 Size if e2!=888997

/*Multomodal transport (QD7)*/
tab d7
tab d7 Size, column nofreq
tab d7 Size

/*Sources qE25-e27*/
tab e25_1
tab e25_2

tab e25_1 Size if e25_1 !=888997, column nofreq
tab e25_1 Size if e25_1 !=888997

tab e25_2 Size if e25_2 !=888997, column nofreq
tab e25_2 Size if e25_2 !=888997


tab e26x1
gen e26x1_corrected=e26x1_1
label define e26x1_corrected 3 "Both options" 4 "not applicable"
label values e26x1_corrected e26x1_corrected
recode e26x1_corrected .=3 if Serial==20214
recode e26x1_corrected .=3 if Serial==3027
recode e26x1_corrected .=3 if Serial==5048
recode e26x1_corrected .=3 if Serial==5058
recode e26x1_corrected .=3 if Serial==5085
tab e26x1_corrected

gen shipment_organization= e26_1 if (Serial !=20214 & Serial!=3027 & Serial !=5048 & Serial!=5058 & Serial!=5085)
replace shipment_organization=e26x1_corrected if shipment_organization==.
replace shipment_organization=e26x1_corrected if shipment_organization==95
label define shipment_organization 1 "From source country" 2 "Within Europe" 3 "Both" 4"Not applicable"
label values shipment_organization shipment_organization
tab shipment_organization if shipment_organization !=888997
tab shipment_organization Size if shipment_organization !=888997, column nofreq


/*Shipment organization for those who handle import from Asia and America, small size*/
tab e25_1 shipment_organization if e25_1 !=888997 & shipment_organization !=888997 & Size==1, row nofreq
tab e25_2 shipment_organization if e25_2 !=888997 & shipment_organization !=888997 & Size==1, row nofreq

/*Shipment organization for those who handle imports from Asia ans America, medium/large size*/
tab e25_1 shipment_organization if e25_1 !=888997 & shipment_organization !=888997 & Size==2, row nofreq
tab e25_2 shipment_organization if e25_2 !=888997 & shipment_organization !=888997 & Size==2, row nofreq
 /*Qe27-sea port most likely used for goods coming from East Asia?*/
 gen sea_port_corrected_asia=e27
 gen e27x1_corrected=e27x1_1
 recode e27x1_corrected 5=7 if Serial==5039
 recode e27x1_corrected 5=7 if Serial==5177
 recode e27x1_corrected 5=7 if Serial==200
 replace sea_port_corrected_asia=e27x1_corrected if sea_port_corrected_asia==95
 label define sea_port_corrected_asia 1 "Thessaloniki" 2 "Rotterdam" 3 "Bar" 4 "Northern Adriatic ports" 5 "Air transfer" 6 "Amsterdam" 7 "Not applicable"
 label values sea_port_corrected_asia sea_port_corrected_asia
 tab sea_port_corrected_asia
 
 tab sea_port_corrected_asia Size,column nofreq
 tab sea_port_corrected_asia Size
 
 /*Qe28-sea port most likely used for goods coming from NA*/
 gen sea_port_corrected=e28
 gen e28x1_corrected=e28x1_1
 recode e28x1_corrected 5=4 if Serial==3034
 recode e28x1_corrected 5=7 if Serial==5039
 recode e28x1_corrected 5=6 if Serial==50
 replace sea_port_corrected=e28x1_corrected if sea_port_corrected==95
 label define sea_port_corrected 1 "Thessaloniki" 2 "Rotterdam" 3 "Bar" 4 "Northern Adriatic ports" 5 "Air transfer" 6 "Hamburg" 7 "Not applicable"
 label values sea_port_corrected sea_port_corrected
 tab sea_port_corrected if sea_port_corrected !=888997
 
 tab sea_port_corrected Size if sea_port_corrected !=888997,column nofreq
 tab sea_port_corrected Size if sea_port_corrected !=888997
 
 /*Qe29-regardless of origin, wich northern adriatic port use the most*/
 tab e29_1 
 tab e29_2
 tab e29_3 
 tab e29_4 

 
 /*Factors that may determine route choice*/
 tab e30_1 if e30_1 !=888997
 tab e30_2 if e30_2 !=888997
 tab e30_3 if e30_3 !=888997 
 tab e30_4 if e30_4 !=888997
 
 gen first_factor_routechoice=1 if e30_1==1
 recode first_factor_routechoice .=2 if e30_2==1
 recode first_factor_routechoice .=3 if e30_3==1
 recode first_factor_routechoice .=4 if e30_4==1
 label define first_factor_routechoice 1 "Ocean freight costs" 2 "Overland freight cost" 3 "Border costs/delays other countries" 4 "Border costs/delays Serbia"
 label values first_factor_routechoice first_factor_routechoice
 tab first_factor_routechoice 
 
 tab first_factor_routechoice Size, column nofreq
 tab first_factor_routechoice Size
 
 
 ***************************////////SECTION III-TIME AND OPERATIONS////////*******************************************************************************
 
 /*Qe2a Expected time for a typical shipment*/
 tab e2a
 
 /*Expected time for a typical shipment according to size*/
 tab e2a Size, column nofreq
 
 /*Expected time according to type of product*/
tab e2a agri_products if agri_products==1, column nofreq 
tab e2a raw_materials if raw_materials==1, column nofreq
tab e2a food_products if food_products==1, column nofreq
tab e2a manufactures if manufactures==1, column nofreq


/*Qe3- Additional unexpected time*/
tab e3

/*Delays per size*/
tab e3 Size if e3 !=888997, column nofreq /*important differences here, better to make graph*/

/*delays per type of product*/
tab e3 agri_products if agri_products==1 & e3!=888997, column nofreq 
tab e3 raw_materials if raw_materials==1 & e3!=888997, column nofreq
tab e3 food_products if food_products==1 & e3!=888997, column nofreq
tab e3 manufactures if manufactures==1 & e3!=888997, column nofreq


/*Qe4-Consolidation of shipments risk delays?*/
tab e4 Size if e4 !=888997,column nofreq
tab e4 Size if e4 !=888997
tab e4 if e4 !=888997

/*Qe6- do intensive physical inspections lead to extended delays?*/
tab e6 if e6 !=888997
tab e6 Size if e6 !=888997, column nofreq
tab e6 Size if e6 !=888997

/*Recoding option "other" for e7*/
gen e7_other=e7x1_1
recode e7_other 5=1 if Serial==2126
recode e7_other 5=7 if Serial==2140
recode e7_other 888995=4 if Serial==71
recode e7_other 6=5 if Serial==20200
recode e7_other 888995=7 if Serial==2034
recode e7_other 888995=7 if Serial==171
recode e7_other 6=7 if Serial==184
recode e7_other 5=1 if Serial==5091
recode e7_other 5=7 if Serial==5081
recode e7_other 4=1 if Serial==99999

gen response_delays=e7
replace response_delays=e7_other if e7==95
label define response_delays 1 " Absorb losses/shipment continues as normal" 2 "Redirect shipments" 3 "Driver responds" 4 "No costs involved/No delays"  5 "It depends"   6 "get in touch with customs" 7 "NA"
label values response_delays response_delays
tab response_delays if response_delays !=888997

tab response_delays Size if response_delays !=888997, column nofreq
tab response_delays Size if response_delays !=888997

tab e8
 
 sum e9 if e9 !=888997, detail
 histogram e9 if e9 !=888997, discrete start(0) percent fcolor(navy) lcolor(navy) barwidth(3) xlabel(#5)
 sum e9 if Size==1 & e9!=888997, detail
 sum e9 if Size==2 & e9!=888997, detail
 format e9 %9.0fc
 tabstat e9 if e9!=888997, by (Size) stats(n mean sd q min max) format
 
/*Qe10: top 3 causes for unexpected delays*/
tab e10_1,sort
tab e10_2,sort
tab e10_3,sort

tab e10_1 Size,column nofreq 

tab e10_2 Size if e10_2 !=888997,column nofreq

tab e10_3 Size, column nofreq


tabstat e11_1 if e11_1 !=888997, by (Size)
tabstat e11_2 if e11_2 !=888997, by (Size)
tabstat e11_3 if e11_3 !=888997, by (Size)
tabstat e11_4 if e11_4 !=888997, by (Size)
tabstat e11_5 if e11_5 !=888997, by (Size)
/* NUMBER OF OBSERVATIONS ANSWERING EACH */
tabstat e11_1 if e11_1 !=888997 , stats(mean count)
tabstat e11_2 if e11_2 !=888997 , stats(mean count)
tabstat e11_3 if e11_3 !=888997, stats(mean count)
tabstat e11_4 if e11_4 !=888997 , stats(mean count)
tabstat e11_5 if e11_5 !=888997, stats(mean count)

gen all_ranks=1 if e11_1~=. & e11_2~=. & e11_3~=. & e11_4~=. & e11_5~=.
replace all_ranks=. if e11_1 ==888997 | e11_2 ==888997 | e11_3 ==888997 | e11_4 ==888997 | e11_5 ==888997
/* total of 128 FF responded */
tabstat e11_1 if all_ranks==1 , stats(mean count)
tabstat e11_2 if all_ranks==1 , stats(mean count)
tabstat e11_3 if all_ranks==1, stats(mean count)
tabstat e11_4 if all_ranks==1 , stats(mean count)
tabstat e11_5 if all_ranks==1, stats(mean count)

tabstat e11_1 if all_ranks==1 , by (Size) stats(mean count)
tabstat e11_2 if all_ranks==1 , by (Size) stats(mean count)
tabstat e11_3 if all_ranks==1, by (Size) stats(mean count)
tabstat e11_4 if all_ranks==1 , by (Size) stats(mean count)
tabstat e11_5 if all_ranks==1, by (Size) stats(mean count)


/*CONTRACT AGREEMENTS*/
tab e12_1 if e12_1 !=888997
tab e12_2 if e12_2 !=888997

tab e13 if e12_1 !=888997
tab e13 Size if e12_1 !=888997

tab e14

tab e15 if e15 !=888997

tab e16 if e16 !=888997
tab e16 if e16 !=888997 & e13==1 
tab e16 Size if e16 !=888997 

sum e16_1 if e16_1 !=888997, detail
tabstat e16_1 if e16_1 !=888997, by(Size) stats(mean median count)

graph box e16_1 if e16_1 !=888997, ylabel(#20,angle(0))
/*Outliers are larger than 300 and less than 100*/

format d24 %9.0fc
tabstat d24 if d24a!=888997 &  e16_1~=. & e16_1 !=888997, stat(mean sd q) format


tab e17

tab e17x1

tab e19

tab e20 if e20 !=888997

tab e21
tab e21 if e20==1
tab e21 Size

tab e22
tab e22 if e20==1
tab e22 Size

tab e23

tab e24

tab e44 Size,column nofreq
tab e44 

tab e45 Size, column nofreq
tab e45 

tab  e46 Size, column nofreq
tab  e46 

tab e47 Size, column nofreq
tab e47 


/*** COLD STORAGE PRODUCTS ***/
tab e43
tab e43 Size


/*** EXPLORING FURTHER RELATIONSHIP BETWEEN SIZE BY NUMBER OF EMPLOYEES AND NUMBER OF SHIPMENTS HANDLED ***/
/*b)QD2: Number of import declarations*/

format shipments_number %9.0fc
tabstat shipments_number if shipments_number!=888997,by(Size) stat( n mean sd q) format

sort Size
by Size: summ shipments_number if shipments_number!=888997, det


/***QUESTION ON IN HOUSE CLEARANCE ***/
tab e31

tab e32a_2
/* some responses make no sense - they must be thinking of something other than what we call IHC - so focus below on FF starting in 2011 or later */
tab e32a_1 if e32a_2>2010 & e32a_2~=.
tab e32a_1 if e32a_2==2011
tab e32a_1 if e32a_2==2012
tab e32a_1 if e32a_2==2013
tab e32a_1 if e32a_2==2014


/*** QUESTION ON CUSTOMS OFFICES HOW MANY USED AND RANKINGS ***/
tab e34 

tab e35b_1, sort 
tab e35b_2, sort
tab e35b_3, sort

tab  e35aa

tab e36_1, sort
tab e36_2, sort
tab e36_3, sort
tab e36_4, sort

tab e37


tab e39
tab e39 Size, column nofreq


********** CROSS-TABS EXPLORING IDENTITY OF RESPONDENTS - TRADE-OFFS IF COSTS ARE CHOSEN IN 1 TRADE-OFF ARE THEY IN THE OTHERS TOO?
gen cost_prefer=1 if (e44==1 | e44==2)
tab e45 
tab e45 if cost_prefer==1
tab  e46 
tab e46 if cost_prefer==1
tab e47 
tab e47 if cost_prefer==1

gen cost_prefer2=1 if (e45==1 | e45==2)
tab e44
tab e44 if cost_prefer2==1
tab  e46 
tab e46 if cost_prefer2==1
tab e47 
tab e47 if cost_prefer2==1

count if (e44==1 | e44==2) & (e45==1 | e45==2) & e47==1