topic Re: Unique Records in SAS Programming

Unique Records

SJN — Wed, 30 May 2018 06:53:17 GMT

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,

Re: Unique Records

PeterClemmensen — Wed, 30 May 2018 07:04:46 GMT

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;

Re: Unique Records

SJN — Wed, 30 May 2018 11:32:11 GMT

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.

Re: Unique Records

PeterClemmensen — Wed, 30 May 2018 12:21:29 GMT

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

Otherwise, remember to mark the thread as accepted 🙂

Regards.

Re: Unique Records

SJN — Wed, 30 May 2018 12:24:24 GMT

Sure 🙂

Re: Unique Records

mkeintz — Wed, 30 May 2018 15:14:41 GMT

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.

Re: Unique Records

SJN — Tue, 05 Jun 2018 05:06:02 GMT

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

Re: Unique Records

ruchi11dec — Tue, 05 Jun 2018 12:41:33 GMT

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;