BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
yangtaotai
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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;

View solution in original post

3 REPLIES 3
Haikuo
Onyx | Level 15

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;

yangtaotai
Calcite | Level 5

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

Haikuo
Onyx | Level 15

Wow. That was some loaded comments Smiley Happy. 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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 4310 views
  • 3 likes
  • 2 in conversation