Contributor
Posts: 30

# Unique Records

Hi All,

Looking for logic help

My sample data

Number Code
1           a1
1           a2
2           b
2           b
3           a1
3           c

Here Number 1 is initially in Code a1 and converted to a2 the same Number 3 converted yo a1 to c

My requirement is looking for Number 2 (i,e. customers who didnt changes the plancode)

Regards,

SJ

PROC Star
Posts: 1,209

## Re: Unique Records

[ Edited ]

And what do you want to do then? Do you want to have all the records for Number=2 in a data set or?

If so, do something like this

``````data have;
input Number Code \$;
datalines;
1 a1
1 a2
2 b
2 b
3 a1
3 c
;

proc sql;
create table want as
select * from have
group by Number
having count(distinct Code)=1;
quit;
``````
Contributor
Posts: 30

## Re: Unique Records

Thank you for the code your code worked for me I used other filter conditions as well. There are some data issues I'm working on it.

PROC Star
Posts: 1,209

## Re: Unique Records

Ok. Let me know if there is anything else I can do.

Otherwise, remember to mark the thread as accepted

Regards.

Contributor
Posts: 30

Sure

Posts: 1,309

## Re: Unique Records

If you values are grouped by NUMBER, then this data step is efficient:

``````data want;
do ncodes=1 by 1 until (last.number);
do until (last.code);
set have;
by number code notsorted;
end;
end;
if ncodes=1;
run;``````

The BY statement uses the NOTSORTED option because while the data is probably sorted by number, it may not be sorted by code-within-number.  So this program just takes consecutive instances of a given number, and counts the number of changes to code within that number.

Contributor
Posts: 30

## Re: Unique Records

Just want understand what happens when we write the do loop before set statement.

The same loop if I'm writing after the set statement it's making an infinite loop. Can you explain me please

Occasional Contributor
Posts: 17

## Re: Unique Records

if looking for base sas solution :

data want(keep = number code);
set have;
by number;
retain code_new;
if first.number then code_new = code;
else do; if code = code_new then change = "n"; end;
if change = 'n';
run;

Discussion stats
• 7 replies
• 151 views
• 3 likes
• 4 in conversation