topic Re: I want to compare the data with previous row in SAS Programming

I want to compare the data with previous row

hema_bhure — Wed, 08 Feb 2023 16:38:37 GMT

Hi ,

Thanks in advance i need help in the following query.

The data i have as follows

The input dataset

-----------------------------------

acc subac amt

111 1 -120.83

111 2 -82.04

111 2 10.04

212 6 34.89

212 6 10.02

123 1 785.90

123 1 `1900.09

so I need data need to compare the current amt with previous amt on basis of account and subac and if amt is diff then raise a flag=1

The following dataset i want.

------------------------------------------

acc subac amt flag

111 1 -120.83 0

111 2 -82.04 0

111 2 10.04 1

212 6 34.89 0

212 6 10.02 1

123 1 785.90 0

123 1 1900.09 1

please help me out.

Re: I want to compare the data with previous row

Tom — Wed, 08 Feb 2023 17:12:20 GMT

The DIF() function will compare the current value with the value passed to it before. So you must execute it on every observation to have it compare to the value on the previous observation.

Do you want the flag to be zero (FALSE) for the first observation in a group?

Your data is not actually sorted, but it does appear to be grouped, so use the NOTSORTED keyword on the BY statement.

data want;
 set have;
 by acc subac notsorted;
 flag = dif(amt) and not first.subac;
run;

Re: I want to compare the data with previous row

ballardw — Wed, 08 Feb 2023 18:57:12 GMT

In your want example you show

The following dataset i want.
------------------------------------------
acc  subac    amt      flag
111    1       -120.83    0
111    1       -120.83    0
111     2       -82.04     0
111     2        10.04      1

So why is -82.04 NOT considered different than -120.83 ?????

@hema_bhure wrote:

Hi ,

Thanks in advance i need help in the following query.

The data i have as follows

The input dataset

-----------------------------------

acc subac amt

111 1 -120.83

111 1 -120.83

111 2 -82.04

111 2 10.04

212 6 34.89

212 6 10.02

123 1 785.90

123 1 785.90

123 1 `1900.09

so I need data need to compare the current amt with previous amt on basis of account and subac and if amt is diff then raise a flag=1

The following dataset i want.

------------------------------------------

acc subac amt flag

111 1 -120.83 0

111 1 -120.83 0

111 2 -82.04 0

111 2 10.04 1

212 6 34.89 0

212 6 10.02 1

123 1 785.90 0

123 1 785.90 0

123 1 1900.09 1

please help me out.