topic Re: Add a label column based on value difference in SAS Programming

Add a label column based on value difference

Crubal — Thu, 17 May 2018 16:08:46 GMT

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!

Re: Add a label column based on value difference

MarkWik — Thu, 17 May 2018 16:27:45 GMT

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

Re: Add a label column based on value difference

Astounding — Thu, 17 May 2018 16:35:30 GMT

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;

Re: Add a label column based on value difference

Reeza — Thu, 17 May 2018 16:44:21 GMT

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.

Re: Add a label column based on value difference

Reeza — Thu, 17 May 2018 16:45:43 GMT

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.

Re: Add a label column based on value difference

Crubal — Thu, 17 May 2018 17:42:13 GMT

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

Re: Add a label column based on value difference

Crubal — Thu, 17 May 2018 17:45:45 GMT

That works. Thanks!