How to update values based on date

[vaidas]

Multiple transaction defined by date and assigned values YES and NO.  Looking forward to write the code that keeps YES until NO starts based on descending condition. If YES is between NO values, should be overwritten as NO. Below few samples, I am dealing with large dataset.

Before change

01/10/2025	YES
01/09/2025	YES
01/08/2025	YES
01/07/2025	YES
01/06/2025	YES
01/05/2025	NO
01/04/2025	YES
01/03/2025	NO
01/02/2025	NO

AFTER CHANGE

01/10/2025	YES
01/09/2025	YES
01/08/2025	YES
01/07/2025	YES
01/06/2025	YES
01/05/2025	NO
01/04/2025	NO
01/03/2025	NO
01/02/2025	NO

[PaigeMiller]

You don't say this, and I hate to assume this is what you mean, but I am going to ask. Does this data set have multiple ID values (perhaps multiple transactions?) Should the conversion of YES to NO be applied to each ID value?

Please make this clear in your description, and also add in any other information that we might need to write the code.

[vaidas]

Valid point. Thanks for asking. Above example was for just for one ID, however there are multiple ID's with multiple transaction dates. YES and NO are for each transaction.  YES should be overwritten  as example above.

[PaigeMiller]

data want;
    set have;
    by transaction_id;
    retain flag;
    if first.transaction_id then flag=0;
    if yn='NO' then flag=1;
    if flag=1 then yn='NO';
    drop flag;
run;

The above code is untested. If you want tested code, you need to provide data as working SAS data step code (examples and instructions), and not as copy/paste from Excel or other program. Since you have more than 1 transaction in your real data, you need to provide us with data that has more than 1 transaction ID so we can test if the code works properly.

[Tom]

The problem seems to be that you want to treat the NO blocks differently based on whether or not they are at the END of of the list of not.

Also you did not provide instructions for what to do when the first observation has a NO value.

Since it is easier to remember the past than predict the future it would probably be easier if the data was sorted by increasing date instead of the decreasing date order you provided.

So assuming you have an ID variable and your date variable is an actual numeric variable with date values that just happens to have a format attached that makes them print in that style (I cannot tell from your printout if you want the dates to print in MDY or DMY order. )

You could do something like this:

data want;
  do until (last.id);
     set have;
     by id date ;
     if flag='YES' then any_yes=1;
     if any_yes then flag='YES';
     output;
  end;
  drop any_yes;
run;

Note that including DATE in the BY statement will allow SAS to detect it the observations are not actually sorted by ascending date values with each ID value.

[Ksharp · Accepted solution]

There are too many scenarios you need to consider about.

data have;
infile cards expandtabs;
input id date : mmddyy10. flag $;
format date mmddyy10.;
cards;
1 01/10/2025	YES
1 01/09/2025	YES
1 01/08/2025	YES
1 01/07/2025	YES
1 01/06/2025	YES
1 01/05/2025	NO
1 01/04/2025	YES
1 01/03/2025	NO
1 01/02/2025	NO
;

proc sql;
create table want as
select *,case when flag='YES'
 and 
(select count(*) from have  where id=a.id and flag='NO' and date<a.date)
and
(select count(*) from have  where id=a.id and flag='NO' and date>a.date)
then 'NO' else flag end as new_flag
 from have as a
  order by id,date desc;
quit;