How to create a column that flags first appeared value from other columns

[Tony_N]

Hi,

I'm working with a dataset looks something like below:

account	month_end	open_indicator
1	30-Nov-19	yes
1	31-Dec-19	no
1	31-Jan-20	no
1	29-Feb-20	no
1	31-Mar-20	no
2	30-Nov-19	yes
2	31-Dec-19	yes
2	31-Jan-20	no
2	29-Feb-20	no
2	31-Mar-20	no
3	30-Nov-19	yes
3	31-Dec-19	yes
3	31-Jan-20	yes
3	29-Feb-20	no
3	31-Mar-20	no

Field description

Account: unique account number

Month_end: date

Open_indicator: indicates whether an account is open or close at the end of the month

I'd like to get help with creating an additional column that gives a flag, let's say 1, ONLY on the first time that an account close, otherwise 0. Below is example of the output i'd like to achieve, where "close" field contains value 1 when an account closes for the first time.

account	month_end	open_indicator	close
1	30-Nov-19	yes	0
1	31-Dec-19	no	1
1	31-Jan-20	no	0
1	29-Feb-20	no	0
1	31-Mar-20	no	0
2	30-Nov-19	yes	0
2	31-Dec-19	yes	0
2	31-Jan-20	no	1
2	29-Feb-20	no	0
2	31-Mar-20	no	0
3	30-Nov-19	yes	0
3	31-Dec-19	yes	0
3	31-Jan-20	yes	0
3	29-Feb-20	no	1
3	31-Mar-20	no	0

Thank you for your help!

[PaigeMiller]

/* UNTESTED CODE */
data want;
    set have;
    by account descending open_indicator;
    if first.open_indicator and open_indicator='no' then close=1;
    else close=0;
run;

Assumes the data set is properly sorted by account and month_end, and that once an account closes, it can't re-open.

[Tony_N]

I got this error "BY variables are not properly sorted on data set", although account and month_end were sorted.

[andreas_lds · Accepted solution]

Posting data in usable form makes it easier to suggest code, so the following step in untested an i assume that data is sorted by account:

data want;
    set have;
    by account;
    length close isClosed 8;
    retain isClosed;
    drop isClosed;

    if first.account then do;
        isClosed = 0;
    end;

    if not isClosed and open_indicator = 'no' then do;
        close = 1;
        isClosed = 1;
    end;
    else do;
        close = 0;
    end;
run;

[Tony_N]

Your code works perfectly andreas_lds. Thank you so much!

[Tony_N]

Hi andreas_lds,

I'm trying to understand how the code you suggested works (since it worked nicely and I'd like to reuse it in a different situation). Would you mind explaining what the following snippet from the code does:

if not isClosed and open_indicator = 'no' then do;
close = 1;
isClosed = 1;
end;

Thanks