Retain question

[hhchenfx]

Hi,

The following question is copied from the below thread

https://communities.sas.com/t5/Base-SAS-Programming/Creating-a-Column-That-Groups-Data-Every-n-Rows/...

I am looking to create a new column that groups my data every n rows. For example I have 1,000 rows, I would like to create a column that assigns the first 100 rows Group 1, the next 100 rows Group 2, and so on until the final row.

My question:

I want to declare grp=1 at the beginning of the code and it doesn't work. I think it is natural to have such line. How can I make that line "grp=1" works?

Thank you.

HHCFX

data have;
do n=1 to 1000;
output;
end;
run;

*SOLUTION;
data want;
set have;
retain grp 1;
if mod(n,100)=0 then grp+1;
run;

*WHY CANT I DECLARE GRP=1;

data want;
set have;
GRP=1;
retain grp;
if mod(n,100)=0 then grp+1;
run;

[hhchenfx]

https://communities.sas.com/t5/Base-SAS-Programming/Creating-a-Column-That-Groups-Data-Every-n-Rows/...

I am looking to create a new column that groups my data every n rows. For example I have 1,000 rows, I would like to create a column that assigns the first 100 rows Group 1, the next 100 rows Group 2, and so on until the final row.

My question:

I want to declare grp=1 at the beginning of the code and it doesn't work. I think it is natural to have such line. How can I make that line "grp=1" works?

Thank you.

HHCFX

data have;
do n=1 to 1000;
output;
end;
run;

*SOLUTION;
data want;
set have;
retain grp 1;
if mod(n,100)=0 then grp+1;
run;

*WHY CANT I DECLARE GRP=1;

data want;
set have;
GRP=1;
retain grp;
if mod(n,100)=0 then grp+1;
run;

[ballardw]

---

@hhchenfx wrote:

Hi,

The following question is copied from the below thread

https://communities.sas.com/t5/Base-SAS-Programming/Creating-a-Column-That-Groups-Data-Every-n-Rows/...

I am looking to create a new column that groups my data every n rows. For example I have 1,000 rows, I would like to create a column that assigns the first 100 rows Group 1, the next 100 rows Group 2, and so on until the final row.

My question:

I want to declare grp=1 at the beginning of the code and it doesn't work. I think it is natural to have such line. How can I make that line "grp=1" works?

Thank you.

HHCFX

---

When you use:

data want;
set have;
GRP=1;
retain grp;
if mod(n,100)=0 then grp+1;
run;

The first thing that happens to each record when read from have is to assign the value of 1 to GRP. Which overwrites any RETAINED value.

Which is one reason that the RETAIN statement has the option of initializing a value. But it would still have headaches if the retained variable were in the data set as the value read from the set would replace any retain as well.

[ballardw]

https://communities.sas.com/t5/Base-SAS-Programming/Creating-a-Column-That-Groups-Data-Every-n-Rows/...

I am looking to create a new column that groups my data every n rows. For example I have 1,000 rows, I would like to create a column that assigns the first 100 rows Group 1, the next 100 rows Group 2, and so on until the final row.

My question:

I want to declare grp=1 at the beginning of the code and it doesn't work. I think it is natural to have such line. How can I make that line "grp=1" works?

Thank you.

HHCFX

[Astounding · Accepted solution]

You can't do that because this statement executes on every observation:

GRP=1;

So the largest value you could ever get for GRP with your program is 2.