How to create table on this data?

[Ganeshk]

Hi,

I have a data as shown in below table:

ID	Group	Subgroup
101	A	AB
102	A	CD
103	A	EF
101	A	GH
104	A	IJ
102	A	KL
501	B	MN
521	B	OP
531	B	QR
501	B	ST
531	B	UV
540	B	WX

From the above table i need to obtain Summary based on Group wise and Subgroup wise.

Note: Within in the group there must be unique Id on overall. EX:101 ID is present in both "AB" and "GH" Subgroup in Group "A". In this case we need to consider only "AB" Subgroup and the other one "GH" can be 0

ID	Group	Subgroup
101	A	AB
102	A	CD
103	A	EF
0	A	GH
104	A	IJ
0	A	KL
501	B	MN
521	B	OP
531	B	QR
0	B	ST
0	B	UV
540	B	WX

Kindly note: ID 101 can be more than twice among the group.

Thanks,

Ganesh K

[LinusH]

Simply put: you wish to replace the if with a 0 whenan id is repeated within a group?
Without knowing the business requirement behind it seem like an odd operation, especially when looking from a data management perspective.
It can be done, resort the data by id an group and reassign id in a data step using by-group processing (hint: if not first.id then...)

[mkeintz]

Your data is apparently sorted by group/subgroup, and you want to reset ID to 0 for any id that has already appeared within the group/subgroup.  As @LinusH said, it's not clear to me why one would do this (as opposed to, say, adding a second variable indicating a given id has already been present), but here is a way to do it in one step:

data want;
  set have;
  by group subgroup;

  length list $2000;
  retain list ' ';
  if first.group then list='';

  if findw(list,cats(id),',') then id=0;  /* Finds a word within a string with comma separators*/
  else list=catx(',',list,id);
  drop list;
run;

Notes:

1. Dataset HAVE is assumed to be already sorted by group/subgroup.
   
   
2. The cats(id) function might seem unnecessary (?concatenating a variable to itself?).  It's advantage here is that it converts numeric to character data without generating an error message.

[Ksharp]

data have;
infile cards truncover expandtabs;
input ID	Group $ Subgroup $;
cards;
101	A	AB
102	A	CD
103	A	EF
101	A	GH
104	A	IJ
102	A	KL
501	B	MN
521	B	OP
531	B	QR
501	B	ST
531	B	UV
540	B	WX
;
run;
data have;
 set have;
 array x{999999} _temporary_;
 if id not in x then do;n+1;x{n}=id;end;
  else id=0;
run;

[mkeintz]

@Ksharp

I  think the OP wants within group unique id's, not neccessarily unique overall.

[Ksharp]

Opps. I must browse it too quickly .

Fixed a typo .

data have;
infile cards truncover expandtabs;
input ID	Group $ Subgroup $;
cards;
101	A	AB
102	A	CD
103	A	EF
101	A	GH
104	A	IJ
102	A	KL
501	B	MN
521	B	OP
531	B	QR
501	B	ST
531	B	UV
540	B	WX
;
run;
data have;
 set have;
 by group;
 array x{999999} _temporary_;
 if first.group then do;n=0;call missing(of x{*});end;
 if id not in x then do;n+1;x{n}=id;end;
  else id=0;
run;

[Ganeshk]

Hi,

I got this output using simple technique:

Proc Sort data=have out=result nodupkey;
by Group ID;
Run;

Any how i tried these possibility also thanks.