Creating a change-variable with unique data set

[Markov]

Hi,

I am dealing with unique data (portfolio holdings) and I want to calculate the change in holdings.

My question arises when there was holdings (a particular stock i of portfolio p) in t-1 period but no holdings of the stock at t (current quarter).

Data set looks like (For given year and quarter, portfolio (p1) is composed of the stocks si):

Portfolios   Year   Qtr   Stock    $amount of stock held by pi ($holdings)   

    p1         2000    1      s1            100

    p1         2000    1      s2            200

    p1         2000    1      s3             50

    p1         2000    1      s4            100

    p1         2000    2      s1            150

    p1         2000    2      s3            100

    p1         2000    2      s4            100

    p1         2000    3      s1            200

    p1         2000    3      s3            100

    p1         2000    3      s4            150

    p1         2000    3      s5            100

Then, after considering change in holdings, the data structure I am looking for is

Portfolios   Year   Qtr   Stock    $amount of stock held by pi    change in holdings   

    p1         2000    2     s1                 150                               50  (=150-100)

    p1         2000    2     s2                  0                               -200  (=0-200 :sold)

    p1         2000    2     s3                 100                               50  (=100-50)

    p1         2000    2     s4                 100                                 0  (=100-100)

    p1         2000    3     s1                 200                                50  (=200-150)

    p1         2000    3     s3                 100                                0  (=100-100)

    p1         2000    3     s4                 150                                50  (=150-100)

    p1         2000    3     s5                 100                              100  (=100-. :new ob)

The key concern is on how to capture the change of stocks that are completely sold from the holdings (in this case s2 in 2nd quarter).

Your help will be greatly appreciated!

Thanks,

Markov              

[barchan]

Try this one:

data hold;
length portfol $4 year qtr 8 stock $4 amt 8;
input portfol--amt;
cards;
p1         2000    1      s1            100
p1         2000    1      s2            200
p1         2000    1      s3             50
p1         2000    1      s4            100
p1         2000    2      s1            150
p1         2000    2      s3            100
p1         2000    2      s4            100
p1         2000    3      s1            200
p1         2000    3      s3            100
p1         2000    3      s4            150
p1         2000    3      s5            100
run;

data prev(rename=(amt=prev_amt));
set hold;
qtr=qtr+1;
year=year;
if qtr=5 then do;
    qtr=1;
    year=year+1;
end;
run;

data change(drop=flag);
merge hold(in=_hold) prev;
by portfol year qtr stock;
if first.qtr & ^_hold then stop;
if _N_>1 &  qtr^=lag(qtr) then flag+1;
if flag then do;
    prev_amt=max(0,prev_amt);
    amt=max(0,amt);
    change=amt-prev_amt;
end;
run;

proc print data=change;
by portfol year qtr;
id portfol year qtr;
run;

[barchan · Accepted solution]

My previous code was wrong because the output in the datastep CHANGE stopped prematurely. The following is better.

data hold;

length portfol $4 year qtr 8 stock $4 amt 8;

input portfol--amt;

cards;

p1         2000    1      s1            100

p1         2000    1      s2            200

p1         2000    1      s3             50

p1         2000    1      s4            100

p1         2000    2      s1            150

p1         2000    2      s3            100

p1         2000    2      s4            100

p1         2000    3      s1            200

p1         2000    3      s3            100

p1         2000    3      s4            150

p1         2000    3      s5            100

p1         2000    4      s3            100

p1         2000    4      s4            150

p1         2000    4      s5            100

p2         2000    1      s1            100

p2         2000    1      s2            200

p2         2000    1      s3             50

p2         2000    1      s4            100

p2         2000    2      s3            100

p2         2000    2      s4            100

p2         2000    3      s1            200

p2         2000    3      s3            100

p2         2000    3      s4            150

p2         2000    3      s5            100

run;

data prev(rename=(amt=prev_amt));

set hold;

qtr=qtr+1;

year=year;

if qtr=5 then do;

    qtr=1;

    year=year+1;

end;

run;

proc sort data=hold(keep=portfol year qtr) out=qtrs nodupkey;

by portfol year qtr;

run;

data change;

merge hold prev;

by portfol year qtr stock;

change=max(0,amt)-max(0,prev_amt);

run;

data out;

merge qtrs(in=_qtrs) change;

by portfol year qtr;

if _qtrs;

run;

options missing="-";

proc print data=out;

by portfol year qtr;

id portfol year qtr;

run;

[Markov]

Thank you Barchan. I just tried this and it seems gave me what I wanted!

[UrvishShah]

Hi,

The following code should help you to find out the change in amount of stock holding across the quarters...I assumed that there are only 3 quarter based on your sample data...If there is 4 qtrs then you need to add some conditions based on it...

proc sort data = portfolio_details;

   by stock;

run;

proc transpose data = portfolio_details

               out  = portfolio_hold(drop = _:)

             prefix = qtr_;

   by stock;

   id qtr;

   var amt_of_stock_held;

run;

data portfolio_hold;

   set portfolio_hold;

   if qtr_2 = . and qtr_3 = . then do;

      change_in_holding = -(qtr_1);

       qtr = 2;

       output;

   end;

   else if qtr_1 = . and qtr_2 = . then do;

      change_in_holding = qtr_3;  

       qtr = 3;

       output;

   end;

   else do;

      change_in_holding = (qtr_2 - qtr_1);

       qtr = 2;

       output;

       change_in_holding = (qtr_3 - qtr_2);

       qtr = 3;

       output;

   end;

run;

proc sort data = portfolio_hold;

   by qtr;

run;

proc sort data = portfolio_details;

   by qtr;

run;

data want;

   merge portfolio_details(in = a drop = stock)

         portfolio_hold(in = b drop = qtr_1 qtr_2 qtr_3);

   by qtr;

   if b then output;

run;

-Urvish

[Markov]

Thank you Urvish! I will try these with my data after modifying it to having 4qtrs.

[Haikuo]

Hi, I am aware that you already have your question answered. This is to offer another one-step hash solution for the sake of some curious minds. The following code uses raw data from 's post.

DATA WANT;

  IF _N_=1 THEN DO;

  DECLARE HASH H1();

H1.DEFINEKEY('_STOCK');

H1.DEFINEDATA('_AMT');

H1.DEFINEDONE();

  DECLARE HASH H0();

H0.DEFINEKEY('_STOCK');

H0.DEFINEDATA('_AMT');

H0.DEFINEDONE();

END;

DO _I=1 BY 1 UNTIL (LAST.QTR);

SET HOLD;

BY portfol year qtr NOTSORTED;

  IF MOD(_N_,2)=1 THEN DO;

IF _I=1 THEN H1.CLEAR();

IF H0.FIND(KEY:STOCK) NE 0 THEN _AMT=0;

  CHANGE_HOLDING = SUM(AMT,-_AMT); 

  _AMT=AMT;

_STOCK=STOCK;

  H1.REPLACE();

  END;

ELSE IF MOD(_N_,2)=0 THEN DO;

IF _I=1 THEN

H0.CLEAR();

IF H1.FIND(KEY:STOCK) NE 0 THEN _AMT=0;

  CHANGE_HOLDING = SUM(AMT,-_AMT); 

  _AMT=AMT;

_STOCK=STOCK;

  H0.REF();

END;

  OUTPUT;

END;

DROP _:;

RUN;

QUIT;

Haikuo

[Markov]

Thank you Hai.Kuo! This is a great help!!!!

Markov

[yeshwanth]

I know you got many answers. I just tried it and got a simpler solution.

data h1;

input port $ year qtr stock  $ amount;

cards;

p1 2000 1 s1 100

p1 2000 1 s2 200

p1 2000 1 s3  50

p1 2000 1 s4 100

p1 2000 2 s1 150

p1 2000 2 s3 100

p1 2000 2 s4 100

p1 2000 3 s1 200

p1 2000 3 s3 100

p1 2000 3 s4 150

p1 2000 3 s5 100

;

run;

data h2(keep = stock amount_1);

set h1;

if qtr=1;

rename amount=amount_1;

run;

proc sort data=h1;by stock;run;

proc sort data=h2;by stock;run;

data h3;

merge h1(in=a) h2(in=b);

by stock;

if a;

final_amt=amount-amount_1;

run;

Hope this helps !