Create Identifiers (based on certain assumptions) and summarize them - BY GROUPS

[Nikos]

Hi all,

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

      or

        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

Nik

[ballardw]

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.

[Nikos]

QST takes the value 2 under more than one assumption

Thank you