How can I find out values which are unique to a group?

[subhroster]

Hi,

I have two columns in a table, Program name and Part numbers. I need to find out the part numbers which are unique to a program Name. The output can contain the same part number within a program but not in any other program.

Here is the dataset.

data have;
input pname $ part $;
datalines;
program1 001
program2 002
program3 001
program3 004
program4 005
program4 005
program5 005
;
run;

Want this

[Reeza · Accepted solution]

Essentially this means you want the parts that have a single program name. 

Or at least that's what it seems but your output doesn't match your description. Part 1 is in Program 1 and Program 3? I thought you may want the opposite but the P005 is in Program 4/5 and you have it included for Program4. 

The output can contain the same part number within a program but not in any other program.

In general, this is how you do something like this though:

proc sql;
create table want as
select * from have where part in (
select part
from have
group by part
having count(distinct pname)=1);


quit;

---

@subhroster wrote:

Hi,

I have two columns in a table, Program name and Part numbers. I need to find out the part numbers which are unique to a program Name. The output can contain the same part number within a program but not in any other program.

Here is the dataset.

data have;
input pname $ part $;
datalines;
program1 001
program2 002
program3 001
program3 004
program4 005
program4 005
program5 005
;
run;

Want this

---

[subhroster]

Hi Reeza

Thanks. This is valid.

[Ksharp]

data have;
input pname $ part $;
datalines;
program1 001
program2 002
program3 001
program3 004
program4 005
program4 005
program5 005
;
run;



data want;
 if _n_=1 then do;
  if 0 then set have;
  declare hash h1();
  h1.definekey('part');
  h1.definedone();
  declare hash h2();
  h2.definekey('part','pname');
  h2.definedone();
 end;
set have;
if h1.check() ne 0 then do;h1.add();h2.ref();output;end;
 else if h2.check()=0 then output;
run;