BookmarkSubscribeRSS Feed
Ganeshk
Obsidian | Level 7

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

6 REPLIES 6
LinusH
Tourmaline | Level 20
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
mkeintz
PROC Star

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.
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Ksharp
Super User
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
PROC Star

@Ksharp

 

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

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Ksharp
Super User

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
Obsidian | Level 7
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 915 views
  • 2 likes
  • 4 in conversation