Hi guys,
suppose to have the following:
data DB1; input cat1 cat2 cat3 cat4 cat5 place value; cards; 0 0 0 0 0 1 125 0 0 0 0 0 2 33 0 0 0 0 0 3 44 0 0 1 0 0 1 2 0 0 1 0 0 2 . 0 0 1 0 0 3 1 0 1 0 0 0 1 . 0 1 0 0 0 2 . 0 1 0 0 0 3 . 0 1 0 1 0 1 65 0 1 0 1 0 2 36 0 1 0 1 0 3 43 0 1 1 0 0 1 . 0 1 1 0 0 2 . 0 1 1 1 0 1 4 0 1 1 1 0 2 2 1 0 0 0 0 1 164 1 0 0 0 0 2 516 1 0 0 0 0 3 619 1 0 1 0 0 1 5 1 0 1 0 0 2 1 1 0 1 0 0 3 2 ;
Is there a way to generate this desired output?
data DB2; input CAT Place Value ; cards; 0 1 125
0 2 33
0 3 44
1 1 169
1 2 517
1 3 621
2 1 69
2 2 38
2 3 43
3 1 7
3 2 1
3 3 3
4 1 69
4 2 38
4 3 43
5 1 0
5 2 0
5 3 0 ;
In other words, I would like to sum all values in column value for each cat* by place 1,2,3. It doesn't matter if cat* are not mutually exclusive. Moreover, the output should be presented as a single column of cat stratified by "place". Can you help me please?
Edit:
looking at the cat5: it has all "0s". As a rule, if all the other cat*(s) in DB1 have 0 (i.e., no "1" index), in DB2 "CAT" will be number "0". This means that cat5 specific values will be 0 because no "1" index is found for that cat* in DB1. Conversely it will be in CAT "0" for values 125, 33 and 44 because all the other cat* are 0 (no "1" index in DB1). Based on this, in DB2 we will see CAT 5 = 0 for place 1,2 and 3 because no specific values are found for cat5 (corresponding to "1" in DB1). For all the other cases, simply a sum of "value" is required for each "CAT*" in DB1 by "place".
Example: let's consider cat1 column in DB1 that will become CAT = 1 in DB2. If we look at all times we see "1"index, for place 1 we will have values in DB1 equal to: 164 and 5. I just need to sum them to get 169. For place 2 we have: 516 and 1 that after the sum will become 517 and for place 3 we have 619 and 2 that after the sum will become 621. This is the output I need.
Note: the sum must be performed REGARDLESS what happens in the other "cat*" in DB1 except for 0 as explained above.
... View more
