BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
VISHALKAPASI
Calcite | Level 5

Dear All,

Greetings for the day...

Well, I want to pair my Sell transactions with Buy transactions considering LIFO logic. This means, whenever I sell shares I want to know its corresponding BUY, who were last bought prior to my Sell.

For example, On 1 st of July, I bought 100, and 25 shares, and sold all 125 shares, So prior to selling 125 shares, my last bought 125 shares were also on 1st of July. So matching price will consider its weighted buy price in desired output. Please find my Input Data in below table.

Trade DateFlagSharesPrice
1-Jul-15Buy100505
1-Jul-15Buy25520
1-Jul-15Sell-125530
2-Jul-15Buy50480
6-Jul-15Buy25492
8-Jul-15Sell-25475
10-Jul-15Buy50503
15-Jul-15Sell-50515
21-Jul-15Sell-50540

Please find below table for Desired output.

Here, in case of 8July Sell, I had bought prior to 8July is  50 shares on 2nd and 25 shares on 6July. Since my last 25 shares bought is for 6July, it will be matched considering LIFO logic and its price will be reflected in my matching price output.

Trade DateFlagSharesPriceLIFO based Matching Price
1-Jul-15Buy100505
1-Jul-15Buy25520
1-Jul-15Sell-125530(25*520+100*505)/125
2-Jul-15Buy50480
6-Jul-15Buy25492
8-Jul-15Sell-2547525*492/25
10-Jul-15Buy50503
15-Jul-15Sell-5051550*503/50
21-Jul-15Sell-5054050*480/50

Requesting for sharing appropriate code considering this LIFO logic where I can get my desired output.

Thanks & Regards,

Vishal


1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

Nothing like a brain teaser to start the day.

data T;

input SHARE $ DATE anydtdte.  FLAG $ VOLUME PRICE;

format DATE date9.;

cards;

A 1-Jul-15    Buy    100    505

A 1-Jul-15    Buy    25     520

A 1-Jul-15    Sell   -50    530

A 1-Jul-15    Sell   -75    530

A 2-Jul-15    Buy    50     480

A 6-Jul-15    Buy    25     492

A 8-Jul-15    Sell   -25    475

A 10-Jul-15   Buy    50     503

A 15-Jul-15   Sell   -50    515

A 21-Jul-15   Sell   -50    540

B 1-Jul-15    Buy    100    505

B 1-Jul-15    Buy    25     520

B 1-Jul-15    Sell   -50    530

B 1-Jul-15    Sell   -75    530

B 2-Jul-15    Buy    50     480

B 6-Jul-15    Buy    25     492

B 8-Jul-15    Sell   -25    475

B 10-Jul-15   Buy    50     503

B 15-Jul-15   Sell   -50    515

B 21-Jul-15   Sell   -50    540

run;

data S;

  array VOLUMES [10] _temporary_;

  array PRICES  [10] _temporary_;

  array LIFO    [10] _temporary_;

  %* populate arrays;

  do until (last.SHARE);                        

    set T ; 

    by SHARE;

    TRANSACTION+1;

    VOLUMES[TRANSACTION]=VOLUME;

    PRICES [TRANSACTION]=PRICE;

  end;

  %* process array for this share;

  do FWD=1 to TRANSACTION;

    if VOLUMES[FWD] < 0 then do ;                   %* we have a sell;

      VOLUME=-VOLUMES[FWD];                       %* store sell volume;

      PRICE =0;                                   %* store cumulated price;

      do BACK=FWD-1 to 1 by -1 until (LIFO[FWD]);     %* look back to find previous buys;

        if VOLUMES[BACK]<= 0 then continue;           %* not a buy ;      

        if VOLUMES[BACK] < -VOLUMES[FWD] then do;     %* volume too small to complete price calculation;

          PRICE+PRICES[BACK]*VOLUMES[BACK];       %* update buy price with this volume;         

          VOLUMES[FWD]+VOLUMES[BACK];             %* reset tally for sell volume;

          VOLUMES[BACK]=0;                        %* reset tally for buy volume;

        end;     

        if VOLUMES[BACK] >= -VOLUMES[FWD] then do;%* price calculation can be completed;

          PRICE+PRICES[BACK]*-VOLUMES[FWD];       %* update buy price;        

          VOLUMES[BACK]+VOLUMES[FWD];             %* reset tally for buy volume;

          LIFO[FWD]=PRICE/VOLUME;                 %* calculate lifo price;

        end;

      end;

    end

  end;

  %* save LIFO prices;

  do I=1 to TRANSACTION;

    set T;                                 

    LIFO_PRICE=LIFO;

    output;

  end;

  call missing (TRANSACTION, of VOLUMES

  • , of PRICES
  • , of LIFO
  • );
  •   keep SHARE DATE FLAG VOLUME PRICE LIFO_PRICE;

    run;

    DATEFLAGVOLUMEPRICELIFO_PRICE
    1-Jul-15Buy100505.
    1-Jul-15Buy25520.
    1-Jul-15Sell-50530512.5
    1-Jul-15Sell-75530505
    2-Jul-15Buy50480.
    6-Jul-15Buy25492.
    8-Jul-15Sell-25475492
    10-Jul-15Buy50503.
    15-Jul-15Sell-50515503
    21-Jul-15Sell-50540480

    Now do this in SQL or using drag'n'drop.. Ha!  Smiley Happy

    View solution in original post

    9 REPLIES 9
    Ksharp
    Super User

    What if you can't find a perfect one ?

    Trade DateFlagSharesPrice
    1-Jul-15Buy100505
    1-Jul-15Buy25520
    1-Jul-15Sell-50530
    VISHALKAPASI
    Calcite | Level 5

    In the above case where perfect qty not found, LIFO logic will consider last bought 50 shares prior to sell. So matching price will be calculated as (25*520+25*505)/50.

    Thanks for the revert.

    Regards,

    Vishal

    ChrisNZ
    Tourmaline | Level 20

    Nothing like a brain teaser to start the day.

    data T;

    input SHARE $ DATE anydtdte.  FLAG $ VOLUME PRICE;

    format DATE date9.;

    cards;

    A 1-Jul-15    Buy    100    505

    A 1-Jul-15    Buy    25     520

    A 1-Jul-15    Sell   -50    530

    A 1-Jul-15    Sell   -75    530

    A 2-Jul-15    Buy    50     480

    A 6-Jul-15    Buy    25     492

    A 8-Jul-15    Sell   -25    475

    A 10-Jul-15   Buy    50     503

    A 15-Jul-15   Sell   -50    515

    A 21-Jul-15   Sell   -50    540

    B 1-Jul-15    Buy    100    505

    B 1-Jul-15    Buy    25     520

    B 1-Jul-15    Sell   -50    530

    B 1-Jul-15    Sell   -75    530

    B 2-Jul-15    Buy    50     480

    B 6-Jul-15    Buy    25     492

    B 8-Jul-15    Sell   -25    475

    B 10-Jul-15   Buy    50     503

    B 15-Jul-15   Sell   -50    515

    B 21-Jul-15   Sell   -50    540

    run;

    data S;

      array VOLUMES [10] _temporary_;

      array PRICES  [10] _temporary_;

      array LIFO    [10] _temporary_;

      %* populate arrays;

      do until (last.SHARE);                        

        set T ; 

        by SHARE;

        TRANSACTION+1;

        VOLUMES[TRANSACTION]=VOLUME;

        PRICES [TRANSACTION]=PRICE;

      end;

      %* process array for this share;

      do FWD=1 to TRANSACTION;

        if VOLUMES[FWD] < 0 then do ;                   %* we have a sell;

          VOLUME=-VOLUMES[FWD];                       %* store sell volume;

          PRICE =0;                                   %* store cumulated price;

          do BACK=FWD-1 to 1 by -1 until (LIFO[FWD]);     %* look back to find previous buys;

            if VOLUMES[BACK]<= 0 then continue;           %* not a buy ;      

            if VOLUMES[BACK] < -VOLUMES[FWD] then do;     %* volume too small to complete price calculation;

              PRICE+PRICES[BACK]*VOLUMES[BACK];       %* update buy price with this volume;         

              VOLUMES[FWD]+VOLUMES[BACK];             %* reset tally for sell volume;

              VOLUMES[BACK]=0;                        %* reset tally for buy volume;

            end;     

            if VOLUMES[BACK] >= -VOLUMES[FWD] then do;%* price calculation can be completed;

              PRICE+PRICES[BACK]*-VOLUMES[FWD];       %* update buy price;        

              VOLUMES[BACK]+VOLUMES[FWD];             %* reset tally for buy volume;

              LIFO[FWD]=PRICE/VOLUME;                 %* calculate lifo price;

            end;

          end;

        end

      end;

      %* save LIFO prices;

      do I=1 to TRANSACTION;

        set T;                                 

        LIFO_PRICE=LIFO;

        output;

      end;

      call missing (TRANSACTION, of VOLUMES

  • , of PRICES
  • , of LIFO
  • );
  •   keep SHARE DATE FLAG VOLUME PRICE LIFO_PRICE;

    run;

    DATEFLAGVOLUMEPRICELIFO_PRICE
    1-Jul-15Buy100505.
    1-Jul-15Buy25520.
    1-Jul-15Sell-50530512.5
    1-Jul-15Sell-75530505
    2-Jul-15Buy50480.
    6-Jul-15Buy25492.
    8-Jul-15Sell-25475492
    10-Jul-15Buy50503.
    15-Jul-15Sell-50515503
    21-Jul-15Sell-50540480

    Now do this in SQL or using drag'n'drop.. Ha!  Smiley Happy

    VISHALKAPASI
    Calcite | Level 5

    Dear Chris,

    Great.!!  You have created LIFO logic code in a Flash.. Amazing..!! Smiley Happy

    Hates off to you Sir..    Big Thanks..

    It will really help me.

    Thanks & Regards,

    Vishal Kapasi

    VISHALKAPASI
    Calcite | Level 5

    Dear Chris,

    I meant Hats off to you Sir, I am amazed with your prompt n perfect reply.

    Pls forgive me for d spelling mistake.

    Thanks &Regards,

    Vishal

    Ksharp
    Super User
    data have;
    input id $ DATE anydtdte.  FLAG $ share price;
    format DATE date9.;
    cards;
    A 1-Jul-15    Buy    100    505
    A 1-Jul-15    Buy    25     520
    A 1-Jul-15    Sell   -50    530
    A 1-Jul-15    Sell   -75    530
    A 2-Jul-15    Buy    50     480
    A 6-Jul-15    Buy    25     492
    A 8-Jul-15    Sell   -25    475
    A 10-Jul-15   Buy    50     503
    A 15-Jul-15   Sell   -50    515
    A 21-Jul-15   Sell   -50    540
    B 1-Jul-15    Buy    100    505
    B 1-Jul-15    Buy    25     520
    B 1-Jul-15    Sell   -50    530
    B 1-Jul-15    Sell   -75    530
    B 2-Jul-15    Buy    50     480
    B 6-Jul-15    Buy    25     492
    B 8-Jul-15    Sell   -25    475
    B 10-Jul-15   Buy    50     503
    B 15-Jul-15   Sell   -50    515
    B 21-Jul-15   Sell   -50    540
    ;
    run;
    
    
    data x;
     set have;
     k+1;
     rename share=_s price=_p;
     keep k share price;
    run;
    data want;
     if _n_ eq 1 then do;
      if 0 then set x;
      declare hash ha(dataset:'x');
      ha.definekey('k');
      ha.definedata('_s','_p');
      ha.definedone();
     end;
    set have nobs=nobs;
    k=_n_; _share=share;sum=0;
    if share lt 0 then do;
     ha.remove();
     do i=1 to nobs;
       k=k-1;
       rc=ha.find(); 
       if rc=0 then do;
         _share=_share+_s;
         if _share lt 0 then do; sum+_s*_p;     ha.remove();end;
           else do;
                 dif=_s-_share;
                 sum+dif*_p;
                 _s=_share;
                 ha.replace(); 
               end;
         *put k= _share= _s= _p= sum= dif=;    
       end;
            if _share ge 0 then leave;
     end;
    LIFO=divide(sum,abs(share));
    end;
    drop k i _s _p sum _share dif rc ;
    run;
    
    
    
    ChrisNZ
    Tourmaline | Level 20

    Yes, you can use hash table, which alleviates the need to size the arrays, but they are harder to understand.

    No need to store the sell operations in the hash table.

    Note that at no point do we check the buy volumes are sufficient to cover the sell volumes.

    Why the heck to spaces get wrecked when pasting and ruin the alignment?

    data HAVE;

    input SHARE $ DATE anydtdte.  FLAG $ VOLUME PRICE;

    format DATE date9.;

    N=_N_;

    cards;

    A 1-Jul-15    Buy 100    505

    A 1-Jul-15    Buy 25     520

    A 1-Jul-15    Sell -50    530

    A 1-Jul-15    Sell -75    530

    A 2-Jul-15    Buy 50     480

    A 6-Jul-15    Buy 25     492

    A 8-Jul-15    Sell -25    475

    A 10-Jul-15   Buy    50 503

    A 15-Jul-15   Sell   -50 515

    A 21-Jul-15   Sell   -50 540

    B 1-Jul-15    Buy 100    505

    B 1-Jul-15    Buy 25     520

    B 1-Jul-15    Sell -50    530

    B 1-Jul-15    Sell -75    530

    B 2-Jul-15    Buy 50     480

    B 6-Jul-15    Buy 25     492

    B 8-Jul-15    Sell -25    475

    B 10-Jul-15   Buy    50 503

    B 15-Jul-15   Sell   -50 515

    B 21-Jul-15   Sell   -50 540

    run;

    data WANT;

      if _N_ eq 1 then do;

        declare hash BUYS(dataset:'HAVE(keep   = N VOLUME PRICE

                                      rename = (VOLUME=_V PRICE=_P)

                                      where  = (_V>0))');

      BUYS.definekey('N');

      BUYS.definedata('_V','_P');

      BUYS.definedone();

        call missing( _V, _P);

      end;

      set HAVE ;

      SUM =0;                                             %* store cumulated price;

      VOLUME_TALLY=VOLUME;                                %* store sell volume;

      if VOLUME < 0 then do N=_N_-1 to 1 by -1 until (LIFO); %* we have a sell;

      RC=BUYS.find();                                     %* look back to find previous buys;

        if RC=0 then do;                                    %* found ;

           if _V < -VOLUME_TALLY then do;                    %* volume too small to complete price calculation;

              SUM+_P*_V  ;                                %* update buy price with this volume;        

              VOLUME_TALLY+_V;                            %* reset tally for sell volume;

              BUYS.remove();                              %* remove buy volume;       

            end;

            else do;                                            %* price calculation can be completed;

              SUM+_P*-VOLUME_TALLY;                          %* update buy price;       

              _V+VOLUME_TALLY;   BUYS.replace();             %* reset  buy volume;

              LIFO=SUM/VOLUME;                               %* calculate lifo price;       

            end;

         end;

      end;

      drop VOLUME_TALLY RC SUM N _: ;

    run;

    Ksharp
    Super User

    "they are harder to understand"

    But are easier to use ( no need to change the size of array ), right ?

    Write once, Use anytime anywhere.

    Xia Keshan

    sas-innovate-2024.png

    Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

    Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

     

    Register now!

    How to Concatenate Values

    Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

    Find more tutorials on the SAS Users YouTube channel.

    Click image to register for webinarClick image to register for webinar

    Classroom Training Available!

    Select SAS Training centers are offering in-person courses. View upcoming courses for:

    View all other training opportunities.

    Discussion stats
    • 9 replies
    • 1936 views
    • 1 like
    • 3 in conversation