SAS: Calculate Running Maximum and Minimum (Using Hash Objects)

Question

I am trying to do the following in SAS. Say that I have the following data coming from an auction book:

I wish to populate the last four columns (Best Buy, Units Avail. at Best Buy, Best Sell, and Units Avail. at Best Sell) in SAS

Anyone one has any suggestion on how to compute the last four columns in SAS?

As you can see, these four last columns keep track of the best available price for a buy and sell and the number of units available at these prices at every period. Every time there is a better buy or sell price added to the "opportunities to buy" or "opportunities to sell" the last two columns must be updated. This is also true when there is a purchased or a sell of some units at the best available price.

Answer 1

Three options I can think of are (1) transpose and use data step arrays, (2) use double-ampersand macro arrays (3) create a hash object. Hash is probably your best bet. Here is a good intro to hash.

I thought about using retain but I don't think that would work because when you buy or sell, it appears that you need to re-calculate the max selling price and min buying price, not including the price you bought or sold at.

Edit: :Below is how I used hash to make this work. First it sets up two datasets, one for selling and one for buying. Then in the next datastep it turns them both into hash objects. It refers to the hash objects when something is bought and sold to update quantity demanded/supplied, and to find the new best price and quantity at that price.

data hashset (keep=time2 buyprice2 ntobuy2 sellprice2 ntosell2);
    set book2;
    buyprice2=buyprice; ntobuy2=ntobuy; sellprice2=sellprice; ntosell2=ntosell; time2=time;
    if (buyprice2 ne .) or (sellprice2 ne .) then output;
run;

data bestprices;
    retain time buyprice ntobuy sellprice ntosell buy sell bestbuy nbestbuy tbestbuy bestsell nbestsell tbestsell;
    set book2 end=setdone;
    if _n_ = 1 then do;
        *Set up hash hh, which contains the data in the data set hashset, with the key time2;
        set hashset;
        declare hash hh(dataset:'hashset', ordered:'a');
        hh.definekey('time2');
        hh.definedata(all:'yes');
        hh.definedone();
        declare hiter hiter('hh'); *Hash iterator allows iterating through the hash;
    end;

    *Buy section;
    if (buyprice ne . and ((bestbuy=.) or (bestbuy>buyprice))) then do;
        bestbuy=buyprice; nbestbuy=ntobuy; tbestbuy=time;
    end;
    bamper=index(buy, '@');
    if bamper>0 then do;
        time2=tbestbuy;
        num=substr(buy, 1, bamper-1)*1;
        if hh.find()=0 then do;
            ntobuy2=ntobuy2-num;
            hh.replace();
            found=0;
            rc=hiter.first();
            do while(rc=0);
                if (ntobuy2>0) and (time2<time) and ((found=0) or (bestbuy>buyprice2)) then do;
                    found=1; 
                    bestbuy=buyprice2; nbestbuy=ntobuy2; tbestbuy=time2;
                end;
                rc=hiter.next();
            end;
        end;
    end;

    *Sell section;
    if (sellprice ne . and ((bestsell=.) or (bestsell<sellprice))) then do;
        bestsell=sellprice; nbestsell=ntosell; tbestsell=time;
    end;
    samper=index(sell, '@');
    if samper>0 then do;
        time2=tbestsell;
        num=substr(sell, 1, samper-1)*1;
        if hh.find()=0 then do;
            ntosell2=ntosell2-num;
            hh.replace();
            found=0;
            rc=hiter.first();
            do while(rc=0);
                if (ntosell2>0) and (time2<time) and ((found=0) or (bestsell<sellprice2)) then do;
                    found=1; 
                    bestsell=sellprice2; nbestsell=ntosell2; tbestsell=time2;
                end;
                rc=hiter.next();
            end;
        end;
    end;
    keep time buyprice ntobuy sellprice ntosell buy sell bestbuy nbestbuy bestsell nbestsell;
    if setdone then hh.output(dataset:'hh');
run;

Here is the code I used to set up the initial dataset. You shouldn't need it because you already have the dataset, but just for reference:

data book;
input buyprice sellprice buy $ sell $ ntobuy ntosell ;
datalines;
80      78      na      na      10  13
80.5    79.5    na      na      12  15
80.4    .       na      na      11  .
81      .       na      na      13  .
80.1    78.1    na      na      12  11
80.2    77      na      na      11  12
82      76      na      na      14  11
.       .       9@80    na      .   .
.       .       1@80    na      .   .
.       78.5    na      na      .   12
.       .       na      4@79.5  .   .
.       79      na      na      .   14
79.5    79.1    na      na      10  13
.       .       na      11@79.5 .   .
79.4    .       na      na      5   .
run;

data book;  
    retain time;
    set book;
    time = _n_;
    if buy = 'na' then buy = '';
    if sell = 'na' then sell = '';
run;