06-28-2012 04:15 PM
I have the attached data file that shows monthly transactions grouped by the variable KEY_V.
Variables POS , NEG and QST are not included in my original dataset.
I want to create them based on the following assumptions:
1.- When AMT_1 = 0 across ALL records of the BY Group Vat KEY_V then var QST = 1 (by groups 1,2 & 3)
2.- When AMT_1 = 0 and ANY of the other AMT_2, AMT_3, AMT_4 has a value different from zero then var QST = 2 (by groups 4,7,8,9)
3.- a) When AMT_1 not equal to zero at the first record of the By Group then POS=1 and NEG=0 (and QST=0). The same pattern is followed until it hits the first record with AMT_1 = 0 then POS=0 and NEG=1 (and QST=0)
b) Extending a) If right after we have more subsequent records with AMT_1 =0 then POS=0 and NEG=0 and
QST =2 if ANY of the other AMT_2, AMT_3, AMT_4 has a value different from zero
QST =3 if ALL of the other AMT_2, AMT_3, AMT_4 equal 0 (by groups 7,9)
Finally I would like to to summarize the counts for POS, NEG and QST both simple counts and CUMULATIVE By ORG_YEAR (as a column ) and TRNS_QTR (as a row).
Any suggestions or hints will be more than welcome.
Thank you in advance
06-28-2012 06:39 PM
Isn't by group 4 in pattern 3? The first value for AMT_1 is not 0. If not your descriptions for pattern 2 needs more clarification.