BookmarkSubscribeRSS Feed
Obsidian | Level 7


Compustat (North American) database provide redemption value of preferred stocks, but Compustat (Global) only have Total Preferred Stock/Redeemable Preferred Stock/Non-Redeemable Prefer Stock data.

I tried to calculate Preferred stocks Redemption value (Reduction in value of preferred stock) by subtracting Total Preferred stock value from its Lagged value(last year).

proc expand data=us1 out=us2  method = none;
by gvkey;
	convert pstk = pstk;	convert pstk = lpstk / transformout=(lag 1);
	label lpstk=lag_pstk;run;

data Us3;
retain pstk lpstk pstk_n pstkrv;
set us2;

I use US data to compare my proxy variable with given "Redumption value of Preferred stock", but my calculated value (diff between current and lagged) give completely different results.


Its not SAS code specific question but I will really appriciate any help, since its one of the most helpful community


Jade | Level 19

Your progam doesn't calculate the "difference between the current and lagged values".  You have the difference between the lagged and current values, i.e. the opposite sign of what is usually calculated in a time series.   Is that intentional?  I.e., is that how compustat defines redemption value of preferred stocks? 


The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

Obsidian | Level 7

thanks alot for replying. I need a value which represent "reduction in the value of preffered stocks"

To clear out, I am calculating firms payout ratio which include dividends and share repurchase. Share repurchase include both common share and preffered shares.

In order to represent a clear picture, part of preffered shares, which have been redeemed (mature / bought back due to underlying date) should not be added (Because firm are not buying them voluntary). This "Preffered Stock Redemption" value is missing in Compustat Global data).

I believe that"Preffered Stock Redemption" value might be captured by reduction in "Total value of Preffered stocks , PSTK" over a year. (yearly difference). For this purpose, I come up with proxy variable which represent "reduction in the value of preffered stocks" as difference between lagged and current "Total value of Preffered Stocks"

[ lpstk-pstk] which is intentional, eg,

2001 > PSTK=250

2002 > PSTK=200

PSTK(2001)-PSTK(2002) = 250 - 200 = 50

Theoratically, this is "reduction in the value of preffered stocks" over a year. But unluckily, when I compared this in Compustat American data (where Preffered stock redemption value is given), my created variable yield completely different values.


Any suggestion or ideas are welcomed. I am sorry if my question is not directly relevant to SAS. I am not aware of any particular forum related to Accounting or Finance with active participation (similar to this SAS forum). I will greatly appriciate if someone can reffer me to any such forum.



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg



Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1 like
  • 2 in conversation