Contributor
Posts: 31

# How to create table on this data?

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

Super User
Posts: 5,884

## Re: How to create table on this data?

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...)
Data never sleeps
Posts: 1,345

## Re: How to create table on this data?

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.
Super User
Posts: 10,784

## Re: How to create table on this data?

``````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;``````
Posts: 1,345

## Re: How to create table on this data?

@Ksharp

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

Super User
Posts: 10,784

## Re: How to create table on this data?

[ Edited ]

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;``````
Contributor
Posts: 31

## Re: How to create table on this data?

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.
Discussion stats
• 6 replies
• 283 views
• 2 likes
• 4 in conversation