DATA Step, Macro, Functions and more

Add a label column based on value difference

Accepted Solution Solved
Reply
Regular Contributor
Posts: 154
Accepted Solution

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

 

test_1.PNG

 

 

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;

View solution in original post


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

Posted in reply to Astounding

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 151 views
  • 1 like
  • 4 in conversation