topic Re: Loop through a column to identify groups in SAS Programming

Loop through a column to identify groups

lizzy28 — Thu, 07 Jul 2022 18:31:43 GMT

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

Re: Loop through a column to identify groups

Reeza — Thu, 07 Jul 2022 19:13:50 GMT

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

Re: Loop through a column to identify groups

s_lassen — Thu, 07 Jul 2022 19:16:13 GMT

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.