Change data based on a row with same id

[raddad34]

I have some data that I pulled for current year orders in a database. The variable changed indicates if they had sensitive information changed in that year. I want to modify the previous entries, or any entries going forward to have changed = 1 if the customer_id is the same and changed = 1 at any entry of that customer_id. If a customer_id doesnt have changed = 1 on any row then I want to leave it alone.

here is a small subset of one customer id as an example.

DATA have;
input order_num customer_id changed
DATALINES;
412 067 0
623 067 0
871 067 1
942 067 0
512 098 0
662 098 0
112 023 0
987 023 1

In this example I'm trying to have customer_id 067 and 023 have changed = 1 for all rows while leaving customer id 098 alone.

like so:

DATA have;
input order_num customer_id changed
DATALINES;
412 067 1
623 067 1
871 067 1
942 067 1
512 098 0
662 098 0
112 023 1
987 023 1

I'm not sure how this can be done so any help would be appreciated. Thanks

[Reeza]

Please provide a larger example that includes more individuals and cases where you would not change the data otherwise any solution is unlikely to work with your actual data.

---

@raddad34 wrote:

I have some data that I pulled for current year orders in a database. The variable changed indicates if they had sensitive information changed in that year. I want to modify the previous entries, or any entries going forward to have changed = 1 if the customer_id is the same and changed = 1 at any entry of that customer_id. If a customer_id doesnt have changed = 1 on any row then I want to leave it alone.

here is a small subset of one customer id as an example.

DATA have;
input order_num customer_id changed
DATALINES;
412 067 0
623 067 0
871 067 1
942 067 0

and I want

DATA have;
input order_num customer_id changed
DATALINES;
412 067 1
623 067 1
871 067 1
942 067 1

I'm not sure how this can be done so any help would be appreciated. Thanks

---

[raddad34]

Sorry about that! I've added more cases in my original post. thanks.

[Reeza · Accepted solution]

SQL option:

proc sql;
create table want as 
select *, max(changed) as want_changed
from have
group by customer_id;
quit;

Data Step Option:

proc sort data=have;
by customer_id descending changed;
run;

data want;
set have;
by customer_ID descending changed;

retain want_changed;

if first.customer_ID then want_changed = changed;
run;

---

@raddad34 wrote:

I have some data that I pulled for current year orders in a database. The variable changed indicates if they had sensitive information changed in that year. I want to modify the previous entries, or any entries going forward to have changed = 1 if the customer_id is the same and changed = 1 at any entry of that customer_id. If a customer_id doesnt have changed = 1 on any row then I want to leave it alone.

here is a small subset of one customer id as an example.

DATA have;
input order_num customer_id changed
DATALINES;
412 067 0
623 067 0
871 067 1
942 067 0
512 098 0
662 098 0
112 023 0
987 023 1

In this example I'm trying to have customer_id 067 and 023 have changed = 1 for all rows while leaving customer id 098 alone.

like so:

DATA have;
input order_num customer_id changed
DATALINES;
412 067 1
623 067 1
871 067 1
942 067 1
512 098 0
662 098 0
112 023 1
987 023 1

I'm not sure how this can be done so any help would be appreciated. Thanks

---

[andreas_lds]

Using a hash-object:

data want;
   set have;

   if _n_ = 1 then do;
      declare hash h(dataset: 'work.have(where= (changed = 1))');
      h.defineKey('customer_id');
      h.defineData('changed');
      h.defineDone();
   end;

   rc = h.find();
   drop rc;
run;

[raddad34]

Thank you for the replies everyone! I really appreciate it!