## How to compare values between rows and do assign the values

Solved
Occasional Contributor
Posts: 19

# How to compare values between rows and do assign the values

Hi Everybody,

I have a table with two columns A and B(just for illustration purpose, there are actually many cols in the table). what I would like to do is compare adjacent rows' B value and if they are equal then let the previous row's B value= current row's A value -0.1. For example, if the original table looks like

A  B

1   2

2   5

3   5

4   5

5   7

7   8

then after modification,  I want to see

A  B

1  2

2   2.9

3  3.9

4  5

5  7

7  8.

I was trying to use the lag function to compare but not sure how to assign the value for B; also considered first. last. but not sure what to do.

Tao

Accepted Solutions
Solution
‎04-16-2015 12:11 PM
Posts: 3,167

## Re: How to compare values between rows and do assign the values

What you need is called 'look-ahead' process:

data have;

input A  B;

cards;

1   2

2   5

3   5

4   5

5   7

7   8

;

data want;

merge have have(firstobs=2 rename=(b=_b a=_a));

if b=_b then

b=_a-0.1;

drop _:;

run;

All Replies
Solution
‎04-16-2015 12:11 PM
Posts: 3,167

## Re: How to compare values between rows and do assign the values

What you need is called 'look-ahead' process:

data have;

input A  B;

cards;

1   2

2   5

3   5

4   5

5   7

7   8

;

data want;

merge have have(firstobs=2 rename=(b=_b a=_a));

if b=_b then

b=_a-0.1;

drop _:;

run;

Occasional Contributor
Posts: 19

## Re: How to compare values between rows and do assign the values

Hi Hai Kuo,

Thank you for your reply and it works. One more thing I want to ask/discuss is that I always feel that in SAS, to manipulate observations or loop through rows are not straight forward. For example, not like other programming language, you can loop through the rows by writing something like for (i=1, i<n, i ++) {} and you know exactly where your current row is by i value. However, in SAS data step, it is hard to know where the current obs/row is ( i guess I can use put to show i though). Like my question in this post, the logic is very simple and I know how to do this in other language but in SAS, i am not sure what to do. In addition, the SAS documents are really hard to follow especially you want to search for something like ":" you use in your post. Even I have read several books for SAS, passed SAS base/advance test, and use SAS for several years, I still feel like it is hard to improve and my knowledge about SAS is very limited.

Therefore, just want to know if there is a better way to learn SAS?

Tao

Posts: 3,167

## Re: How to compare values between rows and do assign the values

Wow. That was some loaded comments . I started learning/using SAS since 2009, and I have been through lots of pains you just mentioned. I am not an expert, but fwiw this is how I learn:

1. Expose yourself to countless SAS problems as much as possible, meaning get actively engaged in SAS-L or here. Not necessarily posting answers, but at least trying to solve them by yourself.

2. Get down to the very basics, as deep as possible. Concepts such as PDV, data step implicit loop, SAS compiling, SAS execution, Macro compiling, Macro execution, etc, you NEED to know them inside-out, not just WHAT and HOW, most importantly, WHY. If you can think like SAS, then SAS will like your code. Read the text-book SAS papers written by Ian Whitlock,  Ron Cody, Paul Dorfman among many others.

Touching down the specifics of your 'loop' comments,  sorry I can't agree with you. In the case of explicit loop, such as Do-Loop, you can use a index to identity the sequence, while in the case of data step implicit loop, _n_ is your guy.

However, to echo with your sentiment, One thing about SAS, good or bad,  is that it has lots of default behaviors for the sake of better efficiency or memory management, keep in mind SAS originated decades ago where there was a completely different world.  Sometimes there is not that much you can do except getting used to it, meaning: memorizing it.

My 2 cents,

Haikuo

🔒 This topic is solved and locked.