BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hhchenfx
Barite | Level 11

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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.

View solution in original post

2 REPLIES 2
ballardw
Super User

@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.

Astounding
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 689 views
  • 1 like
  • 3 in conversation