topic Re: Comparing a row in a dataset with the next row in SAS Enterprise Guide

Comparing a row in a dataset with the next row

SASnewbie2 — Tue, 09 Oct 2018 06:48:05 GMT

Hi All,

Appreciate your advice how can i build an advanced expression to compare the row data with following row data. For instance, I would like to compare the Employee_ID from row 1 with row 2, row 2 with row 3..etc and indicate "True" if the data is the same and "False" if it's different.

Thanks in advance.

Re: Comparing a row in a dataset with the next row

PeterClemmensen — Tue, 09 Oct 2018 06:54:08 GMT

You can do something like this

data want;
   merge SomeData SomeData(firstobs=2 keep=Employee_ID rename=(Employee_ID=leadEmp_ID));
   flag=ifc(Employee_ID=leadEmp_ID, "True", "False");
run;

Re: Comparing a row in a dataset with the next row

SASnewbie2 — Tue, 09 Oct 2018 07:03:49 GMT

Hi Draycut,

Can I apply the code in the advanced expression as i have no knowledge in the SAS programming.

Alternatively, any other simplify way to do it ?

Re: Comparing a row in a dataset with the next row

SASnewbie2 — Tue, 09 Oct 2018 10:32:18 GMT

Hi Draycut,

Here is the output. However, the identical value between row 1 and row 2 are showing "False" instead "True".

Thank you.

Re: Comparing a row in a dataset with the next row

PeterClemmensen — Tue, 09 Oct 2018 10:36:28 GMT

First, let me say, I have no idea about the Advanced Expression facility in EG. I am a straight up coder 🙂

But to answer your question, all of you Employee_IDs are missing here? So you are comparing a missing value to the number 120103 in your first row, which will definitely result in a False value.

Re: Comparing a row in a dataset with the next row

SASnewbie2 — Tue, 09 Oct 2018 10:40:45 GMT

Hi Draycut,

I copied it from your formula, is that anything I miss out ? as I would like to compare the row 1 value with the subsequent row value in the Employee ID column.

data want;
merge WORK.QUERY_FOR_SALES (firstobs=2 keep=Employee_ID rename=(Employee_ID=leadEmp_ID));
flag=ifc(Employee_ID=leadEmp_ID, "TRUE", "FALSE");
run;

Re: Comparing a row in a dataset with the next row

PeterClemmensen — Tue, 09 Oct 2018 10:43:36 GMT

If you look at my code, I specify the same data set twice in the Merge Statement on purpose. Thus, I think your code should be

data want;
merge WORK.QUERY_FOR_SALES WORK.QUERY_FOR_SALES (firstobs=2 keep=Employee_ID rename=(Employee_ID=leadEmp_ID));
flag=ifc(Employee_ID=leadEmp_ID, "TRUE", "FALSE");
run;

Re: Comparing a row in a dataset with the next row

SASnewbie2 — Tue, 09 Oct 2018 10:54:03 GMT

Hi Draycuts,

It works now. Thanks a lots.

May i know what does the " firstobs=2 " means ? and "Keep Employee_ID" means remain the Employee_ID field ?

merge WORK.QUERY_FOR_SALES WORK.QUERY_FOR_SALES (firstobs=2 keep=Employee_ID rename=(Employee_ID=leadEmp_ID));
flag=ifc(Employee_ID=leadEmp_ID, "TRUE", "FALSE");

Thanks

Re: Comparing a row in a dataset with the next row

PeterClemmensen — Tue, 09 Oct 2018 11:02:04 GMT

Cool, glad to hear that 🙂

firstobs=2 means that you read from the second record in the data set. In the Merge Statement, we read the entire dataset and the employee_ID variable once more. Since this is the only variable of interest, we use the Keep=Employee_ID to make sure that we only read this variable here. Finally, we use the rename= option to rename the variable looking ahead.

Re: Comparing a row in a dataset with the next row

TomKari — Tue, 09 Oct 2018 13:55:34 GMT

Hi, welcome to the SAS world!

I'm glad you got the results you want. Let me add a little to your background knowledge.

Technically, you can't compare one row to the one before in the "Advanced Expression" builder, because it uses SQL, and SQL conceptually doesn't recognize the concept of sequentially comparing rows.

However, it looks to me like what you're trying to do is to find instances where there are duplicate employee ids in your file. This can be done using the SQL operations in the Query Builder. If you need more advice on how to do it, just reply to this.

Tom

Re: Comparing a row in a dataset with the next row

Astounding — Tue, 09 Oct 2018 14:14:51 GMT

SAS actually supplies simple but powerful tools that let you accomplish this. Consider:

data want;

set have;

by Employee_ID notsorted;

length newvar $ 5;

if last.Employee_ID=1 then newvar='True';

else newvar='False';

run;

The BY statement in a DATA step is "must-have" knowledge.

Re: Comparing a row in a dataset with the next row

SASnewbie2 — Wed, 10 Oct 2018 01:09:07 GMT

Hi Draycut,

Thanks alot for your advice 🙂

Re: Comparing a row in a dataset with the next row

SASnewbie2 — Wed, 10 Oct 2018 01:11:30 GMT

Hi Tom,

Great to hear the alternative solution from you too.

Appreciate you could guide me how can it be done using SQL operations in the Query Builder too.

Thanks in advance 🙂

Re: Comparing a row in a dataset with the next row

TomKari — Wed, 10 Oct 2018 03:33:53 GMT

Just after I posted this, I noticed your other post. I hope you find the suggestions useful!

Tom

Re: Comparing a row in a dataset with the next row

SASnewbie2 — Fri, 12 Oct 2018 10:06:39 GMT

Hi DrayCut,

Require your valuable advice.

data want;
merge WORK.FINAL MOTOR CV TABLE 2 WORK.FINAL MOTOR CV TABLE 2 (firstobs=1 keep=Cn_No rename=(Cn_No=StatusF));
flag=ifc(Cn_No=StatusF, "1", "0");
run;

I have no idea why the error prompt as shown below (The file doesn't exist) if I using appended data set. However, it works fine if i using a dataset which before append

The log shows alright if I run the Truncate data (before append)

Thank you.

Re: Comparing a row in a dataset with the next row

DS07 — Tue, 05 Nov 2019 10:25:20 GMT

Thanks, this code helped me a lot 🙂