DATA Step, Macro, Functions and more

How to create table on this data?

Reply
Contributor
Posts: 29

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,424

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
Trusted Advisor
Posts: 1,018

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,018

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;
Trusted Advisor
Posts: 1,018

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,018

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: 29

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