topic Re: incremental counter that reset in SAS Programming

incremental counter that reset

scottp — Tue, 21 Jun 2011 21:19:43 GMT

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

Re: incremental counter that reset

scottp — Tue, 21 Jun 2011 21:21:57 GMT

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

Re: incremental counter that reset

Ksharp — Wed, 22 Jun 2011 02:13:27 GMT

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

Ksharp

Re: incremental counter that reset

ArtC — Wed, 22 Jun 2011 05:18:56 GMT

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毙

Re: incremental counter that reset

ArtC — Wed, 22 Jun 2011 05:34:54 GMT

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