How do I use SAS Procedure For simple Stock Analysis / SQL?

[blakezen07]

Hi All,

I am still somewhat new to SAS (last year) and haven't used it in a while. Can someone please help with using either Proc sql or other data/Proc steps that come to mind to get me started in answering the following:

1. 3, 6, 12month, 1 year,  2 year:

      - mean Stock returns by company

      - Standard deviations for these time horizons.

2. Create a new data set for 1 and merge that with original data set by company name only.

3. Employ do loops (if/then?) or macros to create another dataset with the same calculations for 1 and 2 above? The aim is for me to understand how you're using do loops or macros for increased efficiency in future tasks like this.

i have attached the CSV file. Any input is appreciated including how you loaded the data (data step with lib reference or Proc import?)

thank you.

[mkeintz]

Your CSV file, presumably the input data, appears to have annual returns for each PERMNO.  But your rolling 2-year returns don't appear to be calculated directly from the reported 1-year returns.  For instance, your file has 2-year return for MACYS 2016 as .1826, but calculating directly from the annual returns (2015=.0640, 2016=.3011), I get .1766  (=sqrt[(1+0.3011)*(1+.0640)]-1).  Are you generating each of your returns independently from from monthly returns? 

If you don't have monthly or quarterly returns, exactly what formula do you want to use to get the requested quarterly and semi-annual returns from the annual returns?   For instance, one could calculate every quarterly return in a given year as (1+annual_return)**0.25 -1)  leading to annual steps between series of 4 identical quarterly returns.  Or you might use PROC EXPAND which can be asked to smooth out those values.

[blakezen07]

Thank you for your note, that's very helpful. 

To to your point, the returns are generated independently. Let's assume that they are accurate.

basically I want to indepedenetply calculate returns and answer 1-3 above. How would I set up Proc expand? I am working on using Proc sql so any guidance on the code for the two would be very helpful.

[mkeintz]

I should have mentioned that PROC EXPAND is part of SAS/ETS.  Do you have the SAS/ETS license?

[blakezen07]

Unfortunately I don't. I used SAS EG one year ago but I only have access to SAS University edition at the moment until next month when I renew my EG license.