LAG Function and Do Loop across 2 variables

[Kimbraun1]

Hi, I'm trying to 1) take the difference between 2 different variables in two consecutive rows, 2) if the difference is < 25 continue to do the same comparison for the next row, 3) output should be in 2 new variables which reflect the first observation and the last observation in the lag. I'm looking for an efficient way to program this. Attached is a sample dataset with the results I would like to see. I am using SAS Enterprise 7.1.

Ideas are welcome!!!

[novinosrin]

Hi @Kimbraun1   I am afraid I can't open attachments but perhaps many people can. For the benefit of people like me, can you please post(as plain text to be able to copy/paste) your 

1. Sample what you HAVE (Input)

2. Same you WANT(Your expected output for the sample input)

3. Brief explanation of the logic and why

Thanks!

PS I am sure somebody probably will open attachment and may do the needful however up to to you

[ballardw]

Do you have a SAS data set yet?

If so please post example data in the form of a data step.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Which variables to do you want to compare specifically? I can guess but that is a poor substitute for proper problem description.

What role is the Subject_id variable supposed to have? You don't mention it in the problem description. One might guess that you don't want to compare across subjects but your problem description does not say so.

Is the second row variable value supposed to be larger than the first row variable? If so, what do want to happen if that is not the case with the actual data?

[Kimbraun1]

Thank you so much for your reply. Are you asking for me to convert my excel into SAS code with datalines?

Subject_id is just the person identifier and there are multiple rows/set of observations for a given person.  I want to compare the second row of apples to the first row of oranges and if the difference is < 25 then I compare the third row of apples to the second row of oranges and if the difference is >25 then I populate a new variable for oranges with the 3rd row value for oranges and a new variable for apples populated with the first row for apples value. If the difference is again <25 then I repeat the previous process until I get a difference within the subject_id that is > 25.

I hope that the explanation helps. Please let me know about the dataset/datalines and I will post accordingly

Best, Kim

[ballardw]

---

@Kimbraun1 wrote:

Thank you so much for your reply. Are you asking for me to convert my excel into SAS code with datalines?

---

No. However since this is a SAS site we would expect code or processes using SAS data sets. If you have a SAS data set then the link I posted will allow you to show the SAS data in a form we can use. Yes that would be using datalines.

Are you stating that Apples on the second row is never less than Oranges on the previous? Since I see that behavior in your data on the first record for the Subject_id then an indication of what should be done is appropriate. I.E something like : "do not compare the first value of Apples for a subject to the last value of Oranges for the previous subject.

Is that the rule involved?

[Kimbraun1]

Hi, Attached is the sample SAS dataset. I hope that this is what you are looking for.

In theory, apples in the second row should never be less than oranges in the first row. If this does occur I would like to flag it as a potential data error, but proceed such that the difference would be negative and fall in the <25 category.

Of note, I'm looking to do these differences, etc within subject_id.  I think that you already deduced this, but wanted to confirm.

Thank you so very much!

Kim