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
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:
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;
I think the OP wants within group unique id's, not neccessarily unique overall.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.