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 1942 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
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 1In 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 1942 067 1
512 098 0
662 098 0
112 023 1
987 023 1I'm not sure how this can be done so any help would be appreciated. Thanks
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 0and I want
DATA have; input order_num customer_id changed DATALINES; 412 067 1 623 067 1 871 067 1942 067 1
I'm not sure how this can be done so any help would be appreciated. Thanks
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 1In 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 1942 067 1
512 098 0
662 098 0
112 023 1
987 023 1I'm not sure how this can be done so any help would be appreciated. Thanks
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.