Hi SAS community,
I have a large dataset (2000 stocks) with 180 month observations. For each stock I have three variables, Returns, Market Cap and transection cost.
I am trying to sort data initially based on transection cost to 4 groups (500 stocks in each group), with group-1 containing stocks with the lowest transection cost and Group-4 containing stocks with the highest transection cost. Then I would like to sort stocks in each group into 10 deciles based on the market cap and calculating returns of each of these deciles (for example decile-1 in group-1 has 50 stocks, so I want to calculate the returns of these stocks). I would like the machine to do the same procedure for each month, so finally I can calculate the average returns of each decile for the full period.
For example: What I am trying to test is to see if stocks with the lowest transection cost and lowest Market Cap (Group-1, Decile-1) have higher average returns than stocks with highest transection cost and highest Market Cap (Group-4, Decile-10).
I know this is too much to ask.
Any ideas or suggestions how I may get this done will be appreciated.
Regards,
1) Sort the data by transaction, then assign group based on a retained count up to 500, then add 1.
2) do this "deciles" thing based on a by group of month.
Not sure what else we can provide with zero information. Test data in the form of a datastep, required output etc.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.