DATA Step, Macro, Functions and more

incremental counter that reset

Reply
Occasional Contributor
Posts: 7

incremental counter that reset

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
Occasional Contributor
Posts: 7

Re: incremental counter that reset

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

Re: incremental counter that reset

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毙
Super User
Posts: 9,678

Re: incremental counter that reset

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


Ksharp
Valued Guide
Posts: 632

Re: incremental counter that reset

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
Ask a Question
Discussion stats
  • 4 replies
  • 742 views
  • 0 likes
  • 3 in conversation