topic Re: Creating a change-variable with unique data set in SAS Procedures

Creating a change-variable with unique data set

Markov — Thu, 20 Feb 2014 07:59:39 GMT

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

Re: Creating a change-variable with unique data set

barchan — Thu, 20 Feb 2014 09:49:21 GMT

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;

Re: Creating a change-variable with unique data set

UrvishShah — Thu, 20 Feb 2014 09:54:51 GMT

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

Re: Creating a change-variable with unique data set

barchan — Thu, 20 Feb 2014 13:08:48 GMT

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;

Re: Creating a change-variable with unique data set

Markov — Thu, 20 Feb 2014 18:35:46 GMT

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

Re: Creating a change-variable with unique data set

Markov — Thu, 20 Feb 2014 18:36:35 GMT

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

Re: Creating a change-variable with unique data set

Haikuo — Mon, 24 Feb 2014 19:32:35 GMT

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

Re: Creating a change-variable with unique data set

Markov — Wed, 26 Feb 2014 20:21:55 GMT

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

Markov

Re: Creating a change-variable with unique data set

yeshwanth — Wed, 26 Feb 2014 21:03:30 GMT

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 !