Hi, I'm trying to add a grouping variable to some data. The variable I want to create is called GROUP below. At the first occurrence of a USUBJID and IDVAR="AESPID", I would like to call that group=1. If there's a second occurrence within that USUBJID of IDVAR="AESPID", I would like to call that group=2 and so on.
I would appreciate any help. Thanks!
USUBJID | IDVAR | IDVARVAL | RELID | GROUP |
X101 | AESPID | 2 | CMAE | 1 |
X101 | CMSPID | 1 | CMAE | 1 |
X101 | CMSPID | 2 | CMAE | 1 |
X101 | AESPID | 5 | CMAE | 2 |
X101 | CMSPID | 1 | CMAE | 2 |
X102 | AESPID | 7 | CMAE | 1 |
X102 | CMSPID | 3 | CMAE | 1 |
X102 | AESPID | 7 | CMAE | 2 |
X102 | CMSPID | 3 | CMAE | 2 |
X102 | CMSPID | 3 | CMAE | 2 |
X102 | AESPID | 8 | CMAE | 3 |
X102 | CMSPID | 5 | CMAE | 3 |
data have;
input (USUBJID IDVAR IDVARVAL RELID) (:$32.);* GROUP;
cards;
X101 AESPID 2 CMAE 1
X101 CMSPID 1 CMAE 1
X101 CMSPID 2 CMAE 1
X101 AESPID 5 CMAE 2
X101 CMSPID 1 CMAE 2
X102 AESPID 7 CMAE 1
X102 CMSPID 3 CMAE 1
X102 AESPID 7 CMAE 2
X102 CMSPID 3 CMAE 2
X102 CMSPID 3 CMAE 2
X102 AESPID 8 CMAE 3
X102 CMSPID 5 CMAE 3
;
data want;
set have;
by usubjid;
if first.usubjid then group=0;
if idvar='AESPID' then group+1;
run;
data have;
input (USUBJID IDVAR IDVARVAL RELID) (:$32.);* GROUP;
cards;
X101 AESPID 2 CMAE 1
X101 CMSPID 1 CMAE 1
X101 CMSPID 2 CMAE 1
X101 AESPID 5 CMAE 2
X101 CMSPID 1 CMAE 2
X102 AESPID 7 CMAE 1
X102 CMSPID 3 CMAE 1
X102 AESPID 7 CMAE 2
X102 CMSPID 3 CMAE 2
X102 CMSPID 3 CMAE 2
X102 AESPID 8 CMAE 3
X102 CMSPID 5 CMAE 3
;
data want;
set have;
by usubjid;
if first.usubjid then group=0;
if idvar='AESPID' then group+1;
run;
proc sort data=input; by usubjid;
run;
data output;
set input;
by usubjid;
retain Group;
if first.Usubjid; then GROUP=0;
if IDVAR = 'AESPID' then GROUP=GROUP+1;
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.