incremental counter that reset

[scottp]

hello,

I'm trying to create a counter that will reset when criteria is met when comparing the previous record to the current record. Please see my code below. for the datalines (below) the counter should the following values: 1, 1,2,1,2,3,1. The counter should reset to one if the prev acct number <> to the current record account number + the current_date-previous_date > 50 + the current amt <> the prev amt.
Thank you for your help,
Scott

data work.mytestdata;
input acct_nbr $ trans_date $ amt;
datalines;
12 18638 20
12 18669 17
12 18711 17
14 18577 14
14 18607 14
14 18669 14
14 18697 12;
run;
data test_output;
set WORK.mytestdata;
cnt=1;
lag_acct = lag1(acct_nbr);
lag_trans_date = lag1(trans_date);
lag_amt = lag1(amt);
do while
(acct_nbr = lag_acct and trans_date-lag_trans_date <=50 and amt = lag_amt);
cnt+1;
end;
run; Message was edited by: scottp

[scottp]

<=50 and amt = lag_amt);
cnt+1;
end;
run; Message was edited by: scottp

[ArtC]

Scott,
portions of your post were truncated. your code may have included < or > signs. Take a look at this link to see how to repost.
http://support.sas.com/forums/thread.jspa?messageID=27609毙

[Ksharp]

Your pose is ambiguous.
It is hard to understand what is your logic and what you want.


Ksharp

[ArtC]

Scottp,
I have made some changes to your code, and have come close to your requested output. Please check your logic for the 6th data row.

Notes:
1) a semicolon in a datalines area signifies end of data (7th row)
2) The DOWHILE was not needed and has been replaced with IF-THEN/ELSE
[pre]data work.mytestdata;
input acct_nbr trans_date amt;
datalines;
12 18638 20
12 18669 17
12 18711 17
14 18577 14
14 18607 14
14 18669 14
14 18697 12
run;
data test_output;
set WORK.mytestdata;
lag_acct = lag1(acct_nbr);
lag_trans_date = lag1(trans_date);
lag_amt = lag1(amt);
if(acct_nbr = lag_acct and trans_date-lag_trans_date le 50 and amt = lag_amt)then cnt+1;
else cnt=1;
run;
title1 'cnt should be 1,1,2,1,2,3,1';
proc print data=test_output;
run;
[/pre]

> hello,
>
> I'm trying to create a counter that will reset when
> criteria is met when comparing the previous record to
> the current record. Please see my code below. for the
> datalines (below) the counter should the following
> values: 1, 1,2,1,2,3,1. The counter should reset to
> one if the prev acct number <> to the current record
> account number + the current_date-previous_date > 50
> + the current amt <> the prev amt.
> Thank you for your help,
> Scott
>
> data work.mytestdata;
> input acct_nbr $ trans_date $ amt;
> datalines;
> 12 18638 20
> 12 18669 17
> 12 18711 17
> 14 18577 14
> 14 18607 14
> 14 18669 14
> 14 18697 12;
> run;
> data test_output;
> set WORK.mytestdata;
> cnt=1;
> lag_acct = lag1(acct_nbr);
> lag_trans_date = lag1(trans_date);
> lag_amt = lag1(amt);
> do while
> (acct_nbr = lag_acct and trans_date-lag_trans_date
> <=50 and amt = lag_amt);
> cnt+1;
> end;
> run;
>
> Message was edited by: scottp