topic ID Comparison in SAS Enterprise Guide

ID Comparison

sasuser_sk — Tue, 31 Mar 2020 20:01:17 GMT

Hi: I am a new SAS EG user and struggling here.

I want to compare ID from row 2 to ID in row 1 for identifying duplicates purposes and c=some other comparison needs. For example:

case when(id = prior(id)) then "Ignore" else "Keep it" end

case when(id = next(id)) then "Ignore" else "Keep it" end

Here prior and next are comparing the id field in row 1 and in row 2.

Can someone please make suggestions?

Thank you!

Re: ID Comparison

PeterClemmensen — Tue, 31 Mar 2020 20:04:12 GMT

You say that this is for identifying duplicates. In that case, there are better ways than to manually compare each id with the id from the previous row. What does your data look like? And what does your desired result look like?

Re: ID Comparison

sasuser_sk — Tue, 31 Mar 2020 20:20:04 GMT

Thanks draycut. My desired result would be to create another column that would let me put filter on it for further analysis. I am aware of the method to identify duplicates by:
Tasks....>Data...>Sort data and then generating a different dataset that has duplicates in it.

I want to keep my existing data set and make a new field that compares the id field.

Hope this helps.

Re: ID Comparison

ballardw — Tue, 31 Mar 2020 20:33:38 GMT

@sasuser_sk wrote:
Thanks draycut. My desired result would be to create another column that would let me put filter on it for further analysis. I am aware of the method to identify duplicates by:
Tasks....>Data...>Sort data and then generating a different dataset that has duplicates in it.

I want to keep my existing data set and make a new field that compares the id field.

Hope this helps.

Adding a new field to your data will create a new data set. You would replace it. It might have the same name but it would not be your "existing data set".

What @PeterClemmensen meant was show use an example of your current data. It doesn't have to be your data but look like it. Then show what the resulting data set with the added column would look like. Best would be to include the example data as data step code. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 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 </> icon or attached as text to show exactly what you have and that we can test code against.

The logic for the created field would also help.

Is your data currently sorted in the order that it needs to be processed?

There may be two different approaches, depending on your data. The RETAIN function to create a variable like "PriorID" to keep the value from the last record, or the LAG function which can reference the values of a variable in previous observations. Caution: this frequently does not work like you may expect if you use IF and LAG in the same statement.

A simple example of Retain:

Data want;
   set have;
	retain Priorid;
	<code>
	/* warning: while processing the 
	first record PriorId is not assigned
	so be aware*/
	/* at the end of the data step
	set the PriorId to the value of the 
	current id*/
	Priorid=id;
run;

When you create the PriorID variable you should make sure that it has the same properties such as length and type as Id.

Re: ID Comparison

Reeza — Tue, 31 Mar 2020 20:35:31 GMT

Look at the SORT task instead and the options. It will identify duplicates or unique records. Very specifically, GROUP BY processing is a unique feature in SAS and you should leverage it. SQL/Query Builder isn't a great way to accomplish this task. A data step is another approach.

proc sort data=have;
by id;
run;

data duplicates unique;
set have;
by;
if not (first.id and last.id) then output duplicates;
else output unique;
run;

@sasuser_sk wrote:

Hi: I am a new SAS EG user and struggling here.

I want to compare ID from row 2 to ID in row 1 for identifying duplicates purposes and c=some other comparison needs. For example:

case when(id = prior(id)) then "Ignore" else "Keep it" end

OR

case when(id = next(id)) then "Ignore" else "Keep it" end

Here prior and next are comparing the id field in row 1 and in row 2.

Can someone please make suggestions?

Thank you!

Re: ID Comparison

sasuser_sk — Tue, 31 Mar 2020 20:42:34 GMT

Yes my data is sorted. here is an example. This is referring to the PRIOR concept I am wanting to use in SAS EG.
Client id 4578 occurs twice. For first instance it is keeping it and then on the second occurrence i want to add ignore. it is identifying duplicate for me as well. Two purposes.

client id NEW FIELD I WANT TO CREATE
2376 KEEP
2489 KEEP
4578 KEEP
4578 IGNORE
5478 KEEP
5784 KEEP
5784 IGNORE
5784 IGNORE
7069 KEEP
7069 IGNORE
567834 KEEP

Re: ID Comparison

Reeza — Tue, 31 Mar 2020 20:44:14 GMT

data want;
set have;
by id;
if first.id then status='Keep';
else status='Ignore';
run;

@sasuser_sk wrote:
Yes my data is sorted. here is an example. This is referring to the PRIOR concept I am wanting to use in SAS EG.
Client id 4578 occurs twice. For first instance it is keeping it and then on the second occurrence i want to add ignore. it is identifying duplicate for me as well. Two purposes.

client id NEW FIELD I WANT TO CREATE
2376 KEEP
2489 KEEP
4578 KEEP
4578 IGNORE
5478 KEEP
5784 KEEP
5784 IGNORE
5784 IGNORE
7069 KEEP
7069 IGNORE
567834 KEEP

Re: ID Comparison

sasuser_sk — Tue, 31 Mar 2020 20:46:20 GMT

Thanks! Can this be don en query builder in SAS EG?

Re: ID Comparison

novinosrin — Tue, 31 Mar 2020 20:53:09 GMT

Hi @sasuser_sk I believe the query builder generates a PROC SQL query and I am afraid the parititon -by feature is unavailable in proc sql and you may have to resort to MONOTONIC(). If query builder allows monotonic in subquery and that we could flag the MIN and others as Keep and Ignore, it's easy and possible. However, I can't confirm if that idea can be plugged in query builder.

Re: ID Comparison

Reeza — Tue, 31 Mar 2020 21:00:52 GMT

Try a RANK task and add the ranks by groups. Then you'll have 1 for the first and a number greater than one for each other record.

@sasuser_sk wrote:
Thanks! Can this be don en query builder in SAS EG?

Re: ID Comparison

novinosrin — Tue, 31 Mar 2020 21:55:41 GMT

What I meant was

proc sql;
create table want as
select *,ifc(min(rn)=rn,'KEEP','IGNORE') as new_variable
from
(select *,monotonic() as rn from have)
group by clientid
order by rn;
quit;

Re: ID Comparison

sasuser_sk — Tue, 31 Mar 2020 22:25:18 GMT

THANK YOU! I'll give it a try.

Re: ID Comparison

sasuser_sk — Tue, 31 Mar 2020 22:25:53 GMT

Thank you everyone for your suggestions.