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

Nikos — Thu, 28 Jun 2012 20:15:31 GMT

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

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

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

ballardw — Thu, 28 Jun 2012 22:39:40 GMT

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.

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

Nikos — Thu, 28 Jun 2012 23:16:05 GMT

QST takes the value 2 under more than one assumption

Thank you

topic Create Identifiers (based on certain assumptions) and summarize them - BY GROUPS in SAS Procedures

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

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

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