## Add a label column based on value difference

Solved
Regular Contributor
Posts: 154

# Add a label column based on value difference

Hi,

I have a dataset called test_1, I would like to add a column named 'Group' such that 'Group' will be numbers from 1, 2, 3, ...

The logic is: New column 'Group' is within each 'Class' and 'DY_TYP',

Start from row 1, 'Group' = 1, if the difference for 'Price' column greater than 4, ''Group' add 1. ( row  2 - row 1 > 4, then on row 2, 'Group' = 2); otherwise 'Group' keeps the same. Same logic happens to row 3 and row 4.

Since the first 4 rows are in a group ('Class' = PC1 and 'DY_TYP'  = WD), It ends.

In this example, we have 'Group' = 1, 2, 2, 3 for the first 4 rows.

And row 5 is 'PC2' for 'Class' column, we will start again from 'Group' = 1, and do the same thing.

How to make this happen in SAS?

Thanks!

Accepted Solutions
Solution
‎05-17-2018 01:45 PM
Super User
Posts: 6,644

## Re: Add a label column based on value difference

[ Edited ]

Assuming your data set is grouped by CLASS DY_TYP:

data want;

set have;

by class dy_typ notsorted;

increase = dif(price);

if first.dy_typ then group = 1;

else if increase > 4 then group + 1;

drop increase;

run;

All Replies
Frequent Contributor
Posts: 102

## Re: Add a label column based on value difference

Post your sample as data step with an expected output sample and not as pics

Solution
‎05-17-2018 01:45 PM
Super User
Posts: 6,644

## Re: Add a label column based on value difference

[ Edited ]

Assuming your data set is grouped by CLASS DY_TYP:

data want;

set have;

by class dy_typ notsorted;

increase = dif(price);

if first.dy_typ then group = 1;

else if increase > 4 then group + 1;

drop increase;

run;

Regular Contributor
Posts: 154

## Re: Add a label column based on value difference

That works. Thanks!

Super User
Posts: 23,357

## Re: Add a label column based on value difference

https://stats.idre.ucla.edu/sas/faq/how-can-i-create-an-enumeration-variable-by-groups/

This tutorial walks through how to add the numeration values using BY group processing.

Super User
Posts: 23,357

## Re: Add a label column based on value difference

I assumed you're programming here and not using DataFlux or DI Studio (since you posted in the Data Management forum). I moved the post to the Base Programming forum, which is more appropriate. If you're using DI Studio the approach would be different.

Regular Contributor
Posts: 154

## Re: Add a label column based on value difference

Thanks @Reeza I am not using DI Studio, but EG. Please move it.

☑ This topic is solved.