Compare two data sets and see if there are differences between certain key variables

[Michael Duarte Goncalves]

Hi Statalist Community,

I'm writing to you because I have a rather complex task to do, and I don't really know how to go about it. I have two sets of data from two different sources, but with a few key variables that allow identification of the municipality (-municipio-), province (-province-), municipal code (-cmunine-), and date (-date-).

These are renewable energy projects accepted by government bodies in Spain. They include several sources (solar, wind, hydro, etc.). For simplicity here, I've only taken 5 observations from each set.

However, I notice visually (using -browse-) that some values from the -using- are not systematically listed in the -master- dataset. Here's an example that reflects this between April 2023 and September 2023 for both datasets:


-master- :

* Example generated by -dataex-. For more info, type help dataex
clear
input str31 municipio str22 province long cmunine int date double(power power_solar cumpower_solar) float(solar_dummy cumsolar_dummy) long renewables_type float N_projects_per_mun
"Colmenar de Oreja" "Madrid" 28043 759 0 0 80 0 4 . 4
"Colmenar de Oreja" "Madrid" 28043 760 0 0 80 0 4 . 4
"Colmenar de Oreja" "Madrid" 28043 761 0 0 80 0 4 . 4
"Colmenar de Oreja" "Madrid" 28043 762 0 0 80 0 4 . 4
"Colmenar de Oreja" "Madrid" 28043 763 0 0 80 0 4 . 4
"Colmenar de Oreja" "Madrid" 28043 764 0 0 80 0 4 . 4
end
format %tm date
label values renewables_type renewables_type

-using- :

* Example generated by -dataex-. For more info, type help dataex
clear
input str30 municipio str20 province float cmunine double(date power) float(power_solar cumpower_solar solar_dummy cumsolar_dummy) long renewables_type float N_projects_per_mun
"Colmenar de Oreja" "Madrid" 28043 759 72500 72500 122500 1 2 5 3
"Colmenar de Oreja" "Madrid" 28043 759 50000 50000  50000 1 1 5 2
"Colmenar de Oreja" "Madrid" 28043 759 75000 75000 197500 1 3 5 1
"Colmenar de Oreja" "Madrid" 28043 760     0     0 197500 0 3 . 3
"Colmenar de Oreja" "Madrid" 28043 761     0     0 197500 0 3 . 3
"Colmenar de Oreja" "Madrid" 28043 762     0     0 197500 0 3 . 3
end
format %tm boe_date_monthly
label values renewables_type encoded_renewables_type
label def encoded_renewables_type 5 "Solar", modify

What I'd like to do:

1. First, find out whether or not the datasets diverge systematically for each of the municipalities at a given time.
2. Secondly, if the -master- does not include certain values, I would like to merge those that are available in the -using-, but not in the “master”.

• Warning: in the -master- and -using-, a month can contain several entries, as shown by the wrong order in the -using- above of the variable -N_projects_per_mun-. This variable is supposed to count the number of projects per municipality over the entire period (2001m1 to 2023m12). It should be cumulative.

Could anyone give me a hand with this, please? I'm more than happy to listen to your suggestions, if what I want is not optimal.
Thank you in advance!

Michael

[Michael Duarte Goncalves]

Hi everyone,

I tried to use the command -cf-, but obtain an error:

. cf municipio province cmunine date year power using "${data_BOE_accepted}/../../../PRETOR_data/stata_mdg_052024/Pretor_Cleaned_Accepte
> dProj_20240508.dta", verbose
master has 83487 obs, using 1228200
r(9);

Could anyone help me on that, please?

Thank you for your help.

Lovely day.
Michael