Unique Records

[SJN]

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

[PeterClemmensen]

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;

[SJN]

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.

[PeterClemmensen]

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

Otherwise, remember to mark the thread as accepted 🙂

Regards.

[SJN]

Sure 🙂

[mkeintz]

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.

[SJN]

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

[ruchi11dec]

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;