Combining results from different collections and export results in excel sheet

[Meth Tuy]

Dear STATA 17 users,
I am new on STATA 17 and I want to use new feature for customizable tables to export results into an already designed table layout in excel.
tha table that I want to produce looks like this:

Province	Dejure			Defacto
	both sex	male	female	both sex	male	female
All
KG
E
N
S
W

Note: I was not able to merge cell using this platform but dejure is located above (both sex, male,female), same on Defacto

The dataset looks like this:

* Example generated by -dataex-. For more info, type help dataex
clear
input double(L01 P03) float(dejure defacto)
1 2 1 1
1 1 1 .
1 1 1 1
1 1 1 .
1 2 1 1
1 2 1 .
1 1 1 1
1 2 1 1
3 1 1 1
1 1 1 1
1 2 1 1
1 1 1 .
1 2 1 .
4 1 1 1
1 1 1 1
1 2 1 1
1 1 1 1
2 2 1 1
1 1 1 1
1 2 1 1
1 1 1 1
1 2 1 1
1 1 1 1
1 2 1 1
1 1 1 1
1 1 1 1
2 2 1 1
1 2 1 1
1 1 1 1
3 1 1 1
1 2 1 1
1 2 1 1
1 2 1 1
1 1 1 1
1 2 1 1
1 1 1 1
1 1 1 1
5 1 1 1
1 2 1 1
1 1 1 1
1 2 1 1
1 1 1 1
5 2 1 1
1 2 1 1
1 2 1 .
1 2 1 1
1 2 1 1
1 2 1 1
2 1 1 1
1 1 1 1
1 2 1 1
1 1 1 .
1 2 1 1
1 1 1 .
1 1 1 .
1 1 1 1
1 1 1 .
1 2 1 1
1 1 1 1
4 1 1 1
1 2 1 1
1 1 1 1
1 2 1 1
1 1 1 1
1 1 1 .
5 1 1 1
1 2 1 1
1 2 1 1
2 2 1 1
1 1 1 1
1 1 1 1
1 1 1 1
1 1 1 1
1 2 1 1
3 1 1 1
1 2 1 1
1 1 1 1
1 1 1 1
1 2 1 1
1 1 1 1
1 1 1 1
1 2 1 1
1 2 1 1
1 2 1 1
1 2 1 1
1 1 1 1
1 1 1 1
1 2 1 1
1 2 1 1
1 1 1 1
1 1 1 1
1 1 1 1
1 1 1 1
1 2 1 1
1 2 1 1
1 1 1 1
1 2 1 1
1 2 1 1
1 1 1 1
1 1 1 1
end
label values L01 L01
label def L01 1 "KG", modify
label def L01 2 "S", modify
label def L01 3 "W", modify
label def L01 4 "N", modify
label def L01 5 "E", modify
label values P03 P03
label def P03 1 "Male", modify
label def P03 2 "Female", modify

code:
collect create Table1a
collect get: table L01 P03 if dejure==1

collect create Table1b
collect get: table L01 P03 if defacto==1

After using the codes above, I got two distinct tables in stata results window. Now my question is: How could I combined the results from the two tables in Stata in order to get one table formatted as the example above.
Thanks

[William Lisowski]

You don't need to combine the results from two tables, you can instead reshape your data and create a single table. Here's sample code using your example data that should start you in a useful direction.

generate id = _n
rename (dejure defacto) (var=)
reshape long var, i(id) j(law) string
table (L01) (law P03), nototals

. generate id = _n

. rename (dejure defacto) (var=)

. reshape long var, i(id) j(law) string
(j = defacto dejure)

Data                               Wide   ->   Long
-----------------------------------------------------------------------------
Number of observations              100   ->   200         
Number of variables                   5   ->   5           
j variable (2 values)                     ->   law
xij variables:
                   vardefacto vardejure   ->   var
-----------------------------------------------------------------------------

. table (L01) (law P03), nototals

-------------------------------------
     |               law             
     |     defacto          dejure   
     |       P03             P03     
     |  Male   Female   Male   Female
-----+-------------------------------
L01  |                               
  KG |    46       42     46       42
  S  |     1        3      1        3
  W  |     3               3         
  N  |     2               2         
  E  |     2        1      2        1
-------------------------------------

.

[Meth Tuy]

Thank you very much William. The codes are helpful but suppose you are working on a big project like population census where you will produce hundreds of tables similar to the the one I shared but from different variables. I am wondering whether there any other ways to develop syntax using STATA commands like collect, table, putexcel etc.
Thanks