topic Re: Change data based on a row with same id in SAS Programming

Change data based on a row with same id

raddad34 — Mon, 13 Dec 2021 18:26:05 GMT

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

Re: Change data based on a row with same id

Reeza — Mon, 13 Dec 2021 18:21:13 GMT

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

Re: Change data based on a row with same id

raddad34 — Mon, 13 Dec 2021 18:26:49 GMT

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

Re: Change data based on a row with same id

Reeza — Mon, 13 Dec 2021 20:28:56 GMT

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

Re: Change data based on a row with same id

andreas_lds — Tue, 14 Dec 2021 08:13:11 GMT

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;

Re: Change data based on a row with same id

raddad34 — Tue, 14 Dec 2021 16:18:02 GMT

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