AWK: Add missing lines in csv file and set specific column to "0"

Question

(Note: this question is heavily shortened because the detailed question was blocked with "This looks like spam.")

I'm having a hard time adding missing lines to a series of data in a semicolon separated csv-file with awk/gawk.

Sample data (csv)

date;city;gender;status;value
2019-10;New York City;women;AL;5
2019-10;New York City;women;SC;2
2019-10;New York City;men;AL;3
2019-10;New York City;men;SC;1
2019-12;New York City;men;AL;5
2019-12;New York City;men;SC;3
2020-01;New York City;women;AL;8
2020-01;New York City;men;SC;2

Goal
For every year (beginning with the first year that appears in the file (here "2019") and ending with the last year that appears in the file (here "2020")) and month (beginning with the first month that appears in the file (here "10") and ending with the last month that appears in the file (here "01")) there should be 4 lines.

The example data for the month "2019-10" show a correct occurence of the data I need:
4 lines for each month of a year, 2 for women, 2 for men each with status AL and SC.

Desired output

date;city;gender;status;value
2019-10;New York City;women;AL;5
2019-10;New York City;women;SC;2
2019-10;New York City;men;AL;3
2019-10;New York City;men;SC;1
2019-11;New York City;women;AL;0
2019-11;New York City;women;SC;0
2019-11;New York City;men;AL;0
2019-11;New York City;men;SC;0
2019-12;New York City;women;AL;0
2019-12;New York City;women;SC;0
2019-12;New York City;men;AL;5
2019-12;New York City;men;SC;3
2020-01;New York City;women;AL;8
2020-01;New York City;women;SC;0
2020-01;New York City;men;AL;0
2020-01;New York City;men;SC;2

Missing lines should get value 0. The original data also includes occurences without a month-line (either for women or men or even no line is delivered).

Any help is greatly appreciated.
As this is my first post on this platform and I'm not natively english speaking please forgive any mistakes I made.

Answer 1

Using any awk in any shell on every Unix box:

$ cat tst.awk
BEGIN {
    FS=OFS=SUBSEP=";"
    split("women" FS "men", genders)
    split("AL" FS "SC", statuses)
}
NR == 1 {
    print
    next
}
{
    vals[$1,$2,$3,$4] = $5
    if ( NR == 2 ) {
        begDate = $1
        city = $2
    }
    endDate = $1
}
END {
    split(begDate,begYm,/-/)
    split(endDate,endYm,/-/)
    for ( yr=begYm[1]; yr<=endYm[1]; yr++ ) {
        begMth = ( yr == begYm[1] ? begYm[2] : 1 )
        endMth = ( yr == endYm[1] ? endYm[2] : 12 )
        for ( mth=begMth; mth<=endMth; mth++ ) {
            date = sprintf("%04d-%02d", yr, mth)
            for ( i=1; i in genders; i++ ) {
                for ( j=1; j in statuses; j++ ) {
                    idx = date FS city FS genders[i] FS statuses[j]
                    print idx, vals[idx]+0
                }
            }
        }
    }
}

$ awk -f tst.awk file
date;city;gender;status;value
2019-10;New York City;women;AL;5
2019-10;New York City;women;SC;2
2019-10;New York City;men;AL;3
2019-10;New York City;men;SC;1
2019-11;New York City;women;AL;0
2019-11;New York City;women;SC;0
2019-11;New York City;men;AL;0
2019-11;New York City;men;SC;0
2019-12;New York City;women;AL;0
2019-12;New York City;women;SC;0
2019-12;New York City;men;AL;5
2019-12;New York City;men;SC;3
2020-01;New York City;women;AL;8
2020-01;New York City;women;SC;0
2020-01;New York City;men;AL;0
2020-01;New York City;men;SC;2

Note that the above will work even if your "city" value contains a -, e.g. Washington-on-the-Brazos, because I do not include - in the FS values and instead call split() in the END section to separate the date into year and month.