I have a database that when grouped by the variables of gender, age, and age_partner result in a very similar table as "general_table". With the slight difference that "general_table" has the variables max_age and nrs. I know how to add those variables, that is not an issue here.
general_table: LOAD * INLINE [ case,gender, age, age_partner, max_age, nrs 1, female, 18, 22, 60, 42 2, female, 19, 20, 60, 41 3, male, 18, 17, 65, 47 ];
How do I create a new table from a previous table, that takes the value of nrs, and creates a column variable, called n, that goes from 0 to the value in nrs?
To illustrate further, in case 1 nrs is equal to 42 so the column would go from 0 to 42, in case 2 nrs is equal to 41 so the column would go from 0 to 41, and the same for case 3.
I have inserted the code of the desired output, I've labeled it as "wanted_table"
PS: I did this in R by using for loops, lists, and bind_rows.
wanted_table: LOAD * INLINE [ case, gender, age, age_partner, max_age, n 1, female, 18, 22, 60, 0 1, female, 18, 22, 60, 1 1, female, 18, 22, 60, 2 1, female, 18, 22, 60, 3 1, female, 18, 22, 60, 4 1, female, 18, 22, 60, 5 1, female, 18, 22, 60, 6 1, female, 18, 22, 60, 7 1, female, 18, 22, 60, 8 1, female, 18, 22, 60, 9 1, female, 18, 22, 60, 10 1, female, 18, 22, 60, 11 1, female, 18, 22, 60, 12 1, female, 18, 22, 60, 13 1, female, 18, 22, 60, 14 1, female, 18, 22, 60, 15 1, female, 18, 22, 60, 16 1, female, 18, 22, 60, 17 1, female, 18, 22, 60, 18 1, female, 18, 22, 60, 19 1, female, 18, 22, 60, 20 1, female, 18, 22, 60, 21 1, female, 18, 22, 60, 22 1, female, 18, 22, 60, 23 1, female, 18, 22, 60, 24 1, female, 18, 22, 60, 25 1, female, 18, 22, 60, 26 1, female, 18, 22, 60, 27 1, female, 18, 22, 60, 28 1, female, 18, 22, 60, 29 1, female, 18, 22, 60, 30 1, female, 18, 22, 60, 31 1, female, 18, 22, 60, 32 1, female, 18, 22, 60, 33 1, female, 18, 22, 60, 34 1, female, 18, 22, 60, 35 1, female, 18, 22, 60, 36 1, female, 18, 22, 60, 37 1, female, 18, 22, 60, 38 1, female, 18, 22, 60, 39 1, female, 18, 22, 60, 40 1, female, 18, 22, 60, 41 1, female, 18, 22, 60, 42 2, female, 19, 20, 60, 0 2, female, 19, 20, 60, 1 2, female, 19, 20, 60, 2 2, female, 19, 20, 60, 3 2, female, 19, 20, 60, 4 2, female, 19, 20, 60, 5 2, female, 19, 20, 60, 6 2, female, 19, 20, 60, 7 2, female, 19, 20, 60, 8 2, female, 19, 20, 60, 9 2, female, 19, 20, 60, 10 2, female, 19, 20, 60, 11 2, female, 19, 20, 60, 12 2, female, 19, 20, 60, 13 2, female, 19, 20, 60, 14 2, female, 19, 20, 60, 15 2, female, 19, 20, 60, 16 2, female, 19, 20, 60, 17 2, female, 19, 20, 60, 18 2, female, 19, 20, 60, 19 2, female, 19, 20, 60, 20 2, female, 19, 20, 60, 21 2, female, 19, 20, 60, 22 2, female, 19, 20, 60, 23 2, female, 19, 20, 60, 24 2, female, 19, 20, 60, 25 2, female, 19, 20, 60, 26 2, female, 19, 20, 60, 27 2, female, 19, 20, 60, 28 2, female, 19, 20, 60, 29 2, female, 19, 20, 60, 30 2, female, 19, 20, 60, 31 2, female, 19, 20, 60, 32 2, female, 19, 20, 60, 33 2, female, 19, 20, 60, 34 2, female, 19, 20, 60, 35 2, female, 19, 20, 60, 36 2, female, 19, 20, 60, 37 2, female, 19, 20, 60, 38 2, female, 19, 20, 60, 39 2, female, 19, 20, 60, 40 2, female, 19, 20, 60, 41 3, male, 18, 17, 65, 0 3, male, 18, 17, 65, 1 3, male, 18, 17, 65, 2 3, male, 18, 17, 65, 3 3, male, 18, 17, 65, 4 3, male, 18, 17, 65, 5 3, male, 18, 17, 65, 6 3, male, 18, 17, 65, 7 3, male, 18, 17, 65, 8 3, male, 18, 17, 65, 9 3, male, 18, 17, 65, 10 3, male, 18, 17, 65, 11 3, male, 18, 17, 65, 12 3, male, 18, 17, 65, 13 3, male, 18, 17, 65, 14 3, male, 18, 17, 65, 15 3, male, 18, 17, 65, 16 3, male, 18, 17, 65, 17 3, male, 18, 17, 65, 18 3, male, 18, 17, 65, 19 3, male, 18, 17, 65, 20 3, male, 18, 17, 65, 21 3, male, 18, 17, 65, 22 3, male, 18, 17, 65, 23 3, male, 18, 17, 65, 24 3, male, 18, 17, 65, 25 3, male, 18, 17, 65, 26 3, male, 18, 17, 65, 27 3, male, 18, 17, 65, 28 3, male, 18, 17, 65, 29 3, male, 18, 17, 65, 30 3, male, 18, 17, 65, 31 3, male, 18, 17, 65, 32 3, male, 18, 17, 65, 33 3, male, 18, 17, 65, 34 3, male, 18, 17, 65, 35 3, male, 18, 17, 65, 36 3, male, 18, 17, 65, 37 3, male, 18, 17, 65, 38 3, male, 18, 17, 65, 39 3, male, 18, 17, 65, 40 3, male, 18, 17, 65, 41 3, male, 18, 17, 65, 42 3, male, 18, 17, 65, 43 3, male, 18, 17, 65, 44 3, male, 18, 17, 65, 45 3, male, 18, 17, 65, 46 3, male, 18, 17, 65, 47 ];
here is a script that should do what you want:
general_table:
LOAD * INLINE [
case,gender, age, age_partner, max_age, nrs
1, female, 18, 22, 60, 42
2, female, 19, 20, 60, 41
3, male, 18, 17, 65, 47
];
//loop through each case
For iCase = 0 to NoOfRows('general_table')-1
//save current column values
LET vCase = peek('case', $(iCase), 'general_table');
LET vGender = peek('gender', $(iCase), 'general_table');
LET vAge = peek('age', $(iCase), 'general_table');
LET vAgePartner = peek('age_partner', $(iCase), 'general_table');
LET vMaxAge = peek('max_age', $(iCase), 'general_table');
LET vNrs = peek('nrs', $(iCase), 'general_table');
//loop through each number from 0 to nrs-value
For iNrs = 0 to $(vNrs)
//create lines
TRACE $(vCase) as case, '$(vGender)' as gender, $(vAge) as age,$(vAgePartner) as age_partner,$(vMaxAge) as max_age, $(iNrs) as nrs;
Concatenate (general_table)
Load $(vCase) as case,
'$(vGender)' as gender,
$(vAge) as age,
$(vAgePartner) as age_partner,
$(vMaxAge) as max_age,
$(iNrs) as nrs
AutoGenerate (1);
next iNrs
next iCase
here is a script that should do what you want:
general_table:
LOAD * INLINE [
case,gender, age, age_partner, max_age, nrs
1, female, 18, 22, 60, 42
2, female, 19, 20, 60, 41
3, male, 18, 17, 65, 47
];
//loop through each case
For iCase = 0 to NoOfRows('general_table')-1
//save current column values
LET vCase = peek('case', $(iCase), 'general_table');
LET vGender = peek('gender', $(iCase), 'general_table');
LET vAge = peek('age', $(iCase), 'general_table');
LET vAgePartner = peek('age_partner', $(iCase), 'general_table');
LET vMaxAge = peek('max_age', $(iCase), 'general_table');
LET vNrs = peek('nrs', $(iCase), 'general_table');
//loop through each number from 0 to nrs-value
For iNrs = 0 to $(vNrs)
//create lines
TRACE $(vCase) as case, '$(vGender)' as gender, $(vAge) as age,$(vAgePartner) as age_partner,$(vMaxAge) as max_age, $(iNrs) as nrs;
Concatenate (general_table)
Load $(vCase) as case,
'$(vGender)' as gender,
$(vAge) as age,
$(vAgePartner) as age_partner,
$(vMaxAge) as max_age,
$(iNrs) as nrs
AutoGenerate (1);
next iNrs
next iCase