Comparing a row in a dataset with the next row

[SASnewbie2]

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.

Thanks in advance.

[PeterClemmensen]

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;

[SASnewbie2]

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 ?

[SASnewbie2]

Hi Draycut, 

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

Thank you.

[PeterClemmensen]

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.

[SASnewbie2]

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;

[PeterClemmensen]

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;

[SASnewbie2]

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 

[PeterClemmensen]

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.

[SASnewbie2]

Hi Draycut,

Thanks alot for your advice 🙂 

[DS07]

Thanks, this code helped me a lot 🙂

[SASnewbie2]

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.

[TomKari]

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

[SASnewbie2]

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 🙂

[TomKari]

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

Tom