Loop through a column to identify groups

[lizzy28]

Hi all,

I want to identify groups through the column "flag" which takes values of missing and 1. When it is missing, it means that a new group starts from the observation; 1 means the same group as the last observation. Below is the sample data with the column "group" to be identified.

Thank you!

ID	flag	group
1	.	1
1	1	1
1	1	1
1	1	1
1	.	2
1	1	2
2	.	1
2	1	1
2	1	1
3	.	1
3	1	1
3	.	2

[Reeza]

data want;
set have;
by id;
retain group;
if first.id then group=0;
if missing(Flag) then group+1;
run;

---

@lizzy28 wrote:

Hi all,

I want to identify groups through the column "flag" which takes values of missing and 1. When it is missing, it means that a new group starts from the observation; 1 means the same group as the last observation. Below is the sample data with the column "group" to be identified.

Thank you!

ID	flag	group
1	.	1
1	1	1
1	1	1
1	1	1
1	.	2
1	1	2
2	.	1
2	1	1
2	1	1
3	.	1
3	1	1
3	.	2

---

[s_lassen · Accepted solution]

Something like this, perhaps?

Data want;
  set have;
  by ID;
  if first.ID then group=1;
  else group+missing(flag);
run;

What it does:

If we get the first ID, GROUP is initialized to 1. Else, we use the SUM statement (like "A+B") to add 1 (SAS sees the logical value TRUE as the number 1) when FLAG is missing. The SUM statement automatically retains the variable GROUP, so we do not have to use a RETAIN statement.

The SUM statement

group+missing(flag);

is the same as 

if missing(flag) then group=sum(group,1);
retain group;

- a very useful shorthand to learn if you are programming in SAS.