DATA Step, Macro, Functions and more

Unique Records

Reply
Contributor SJN
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 SJN
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 Smiley Happy

 

Regards.

Contributor SJN
Contributor
Posts: 30

Re: Unique Records

Sure Smiley Happy

Trusted Advisor
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 SJN
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;

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