sasdo-loops

combining values in a loop in sas


In my do loop, I want the first value in the avail_cashA column to be equal to the pool_payment. And subsequent values in avail_cashA equal to pool_payment + reserve. I tried updating avail_cashA after output, but it gave the incorrect values. I know they're wrong because we did this in excel.

I'm not sure how to make this reproducible without providing the whole code, but he below code is not runnable. It specifically contains the relevant portion.

do Month = 1 to maturity;
    pool_payment = pmt(coupon, maturity - month + 1, pool_net_bal, 0);

*Certificate A;
    avail_cashA = pool_payment;

 *Money leftover;
    Reserve = avail_cashC - actl_pmtC;

    output;
    avail_cashA = pool_payment + reserve;

end;

Solution

  • You update avail_cashA before and after output, so only the value before output will be kept.

    do Month = 1 to maturity;
        pool_payment = pmt(coupon, maturity - month + 1, pool_net_bal, 0);
    
        *Certificate A;
        avail_cashA = pool_payment;             *Before;
        *Money leftover;
        Reserve = avail_cashC - actl_pmtC;
        output;
    
        avail_cashA = pool_payment + reserve;   *After;
    end;
    

    If you just want derive this variable in one way at the very first time, and in another way at all of the following time, try conditional statement:

    do Month = 1 to maturity;
        pool_payment = pmt(coupon, maturity - month + 1, pool_net_bal, 0);
    
        *Certificate A;
        if month=1 then avail_cashA = pool_payment;
        *Money leftover;
        Reserve = avail_cashC - actl_pmtC;
        output;
    
        avail_cashA = pool_payment + reserve;
    end;