topic Retaining previous Value in SAS Programming

Retaining previous Value

Solly7 — Sun, 18 Feb 2024 15:43:22 GMT

I below above data in sas ,so i need to create new columns ewallet_inbundle and cash_inbundle based on the below instructions

if payment_tpye=cash/ewallet for particular acct_no on first occurence of acct_no and service_fee=0 then default value for both ewallet_inbundle and cash_inbundle should be 1, and also if payment_tpye=cash/ewallet for particular acct_no on first occurence and service_fee>0 then value for both ewallet_inbundle and cash_inbundle should be 0 .
From 2nd row to the last one of same acct_no, if payment_type=cash and service_fee>0 then cash_inbundle should be 0 but ewallet_inbundle status should be the one on previous row , also if payment_type=ewallet and service_fee>0 then ewallet_inbundle value should be 0(but retain previous status of cash_inbundle to previous one ..
Also 2nd row to the last one of same acct_no, if payment_type=cash and service_fee=0 then cash_inbundle should be 1 but ewallet_inbundle status should be the one on previous row , also if payment_type=ewallet and service_fee=0 then ewallet_inbundle value should be 1, but retain previous status of cash_inbundle to previous one ..

See below dataset and my code, issue i am having is that on the output data( row 7 i get cash_inbundle value of 1 instead of 0 which was cash_inbundle value of previous row)

data have;
    input acct_no date :date9. trns_amnt payment_type $ service_fee;
    format date date9.;
    datalines;
1 1-Oct-23 500 Ewallet 0
1 15-Oct-23 500 Ewallet 6
1 1-Nov-23 500 Ewallet 2.2
1 15-Nov-23 500 Ewallet 0
2 16-Nov-23 500 Ewallet 0
2 17-Nov-23 500 Cash 5
2 18-Nov-23 500 Ewallet 0
3 1-Dec-23 500 Cash 0
3 2-Dec-23 500 Cash 5.6
3 3-Dec-23 500 Cash 5.5
;
run;

data want;
    set have;
    lagged_ewallet_inbundle = missing(lag(ewallet_inbundle));
    lagged_cash_inbundle = missing(lag(cash_inbundle));
    by acct_no;

    if first.acct_no then do;
        if service_fee = 0 then do;
            ewallet_inbundle = 1;
            cash_inbundle = 1;
        end;
        else do;
            ewallet_inbundle = 0;
            cash_inbundle = 0;
        end;
    end;

    else do;
        if payment_type = 'Cash' then do;
            if service_fee > 0 then do;
                cash_inbundle = 0;
                ewallet_inbundle = lagged_ewallet_inbundle;
            end;
            else do;
                cash_inbundle = 1;
                ewallet_inbundle = lagged_ewallet_inbundle;
            end;
        end;
        else if payment_type = 'Ewallet' then do;
            if service_fee > 0 then do;
                ewallet_inbundle = 0;
                cash_inbundle = lagged_cash_inbundle;
            end;
            else do;
                ewallet_inbundle = 1;
                cash_inbundle = lagged_cash_inbundle;
            end;
        end;
    end;
    drop lagged_ewallet_inbundle lagged_cash_inbundle;
run;

I have also attached output results

Re: Retaining previous Value

Tom — Sun, 18 Feb 2024 16:13:24 GMT

Not sure why you want to test of the lagged value is missing or not, that doesn't make much sense.

But because you are talking about a variable you are CREATING in this step you probably just want to RETAIN the new variables.

Does this work?

data want;
    set have;
    by acct_no;

    retain ewallet_inbundle cash_inbundle;

    if first.acct_no then do;
        if service_fee = 0 then do;
            ewallet_inbundle = 1;
            cash_inbundle = 1;
        end;
        else do;
            ewallet_inbundle = 0;
            cash_inbundle = 0;
        end;
    end;

    else do;
        if payment_type = 'Cash' then do;
            if service_fee > 0 then do;
                cash_inbundle = 0;
            end;
            else do;
                cash_inbundle = 1;
            end;
        end;
        else if payment_type = 'Ewallet' then do;
            if service_fee > 0 then do;
                ewallet_inbundle = 0;
            end;
            else do;
                ewallet_inbundle = 1;
            end;
        end;
    end;
run;

Results

                               trns_    payment_    service_    ewallet_      cash_
OBS    acct_no         date     amnt      type         fee      inbundle    inbundle

  1       1       01OCT2023     500     Ewallet        0.0          1           1
  2       1       15OCT2023     500     Ewallet        6.0          0           1
  3       1       01NOV2023     500     Ewallet        2.2          0           1
  4       1       15NOV2023     500     Ewallet        0.0          1           1
  5       2       16NOV2023     500     Ewallet        0.0          1           1
  6       2       17NOV2023     500     Cash           5.0          1           0
  7       2       18NOV2023     500     Ewallet        0.0          1           0
  8       3       01DEC2023     500     Cash           0.0          1           1
  9       3       02DEC2023     500     Cash           5.6          1           0
 10       3       03DEC2023     500     Cash           5.5          1           0

Re: Retaining previous Value

Solly7 — Sun, 18 Feb 2024 16:22:48 GMT

@Tom Yes this works, thank you appreciate it