topic Re: How can I find out values which are unique to a group? in New SAS User

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

subhroster — Wed, 22 Jan 2020 03:41:22 GMT

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

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

Reeza — Wed, 22 Jan 2020 04:11:17 GMT

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

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

subhroster — Wed, 22 Jan 2020 07:29:58 GMT

Hi Reeza

Thanks. This is valid.

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

Ksharp — Wed, 22 Jan 2020 12:05:32 GMT

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;