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

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              

1 ACCEPTED SOLUTION

Accepted Solutions
barchan
Calcite | Level 5

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;

View solution in original post

8 REPLIES 8
barchan
Calcite | Level 5

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
Calcite | Level 5

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
Calcite | Level 5

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

UrvishShah
Fluorite | Level 6

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
Calcite | Level 5

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

Haikuo
Onyx | Level 15

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
Calcite | Level 5

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

Markov

yeshwanth
Fluorite | Level 6

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 !

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 8 replies
  • 1602 views
  • 6 likes
  • 5 in conversation