Add a label column based on value difference

[Crubal]

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! 

[MarkWik]

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

[Astounding · Accepted solution]

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;

[Crubal]

That works. Thanks! 

[Reeza]

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.

[Reeza]

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.

[Crubal]

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