Hi all,
I have a dataset that are similar to the following. It's the monthly stock prices of about 200 companies. Now I want to generate the monthly return of each stock, which I defined as : (Rt/Rt-1) -1 . How should write the code to do this?
Thank you very much.
Name | DANANG RUBBER | SAIGON THUONG TIN COMMERCIAL | ASIA COMMERCIAL | CHUONG DUONG BEVERAGES | THANG LONG WINE | PETROVIETNAM FCM. | DONG PHU RUBBER | HOA BINH RUBBER | GENERAL MATERIAL | THONG NHAT RUBBER |
Code | VT:DRC(P) | VT:STB(P) | VT:ACB(P) | VT:SCD(P) | VT:VTL(P) | VT:DPM(P) | VT:DPR(P) | VT:HRC(P) | VT:HSI(P) | VT:TNC(P) |
1/1/08 | 13498.75 | 30110.97 | 49638.72 | 40000 | 21994.38 | 74000 | 85000 | 96000.5 | 41000 | 36500 |
2/1/08 | 13169.51 | 29651.26 | 41527.03 | 33600 | 22494.25 | 68500 | 82000 | 84571.88 | 34600 | 31500 |
3/1/08 | 8505.31 | 22525.75 | 30872.86 | 23800 | 20344.8 | 53500 | 58000 | 54000.31 | 25800 | 24200 |
4/1/08 | 6200.65 | 17193.13 | 28481.73 | 17900 | 12896.7 | 49000 | 49400 | 39143.07 | 21300 | 19300 |
5/1/08 | 5816.54 | 15400.27 | 25091.76 | 19100 | 11846.97 | 53500 | 56000 | 35714.48 | 23800 | 19000 |
6/1/08 | 4356.91 | 11079 | 14316.54 | 17700 | 9997.44 | 40200 | 44500 | 26742.99 | 18600 | 14000 |
7/1/08 | 3709.41 | 10343.46 | 14861.35 | 22100 | 12146.89 | 44000 | 54000 | 25428.71 | 18500 | 13500 |
8/1/08 | 4402.99 | 10527.35 | 18523.71 | 18400 | 9897.47 | 58000 | 62000 | 27714.43 | 24100 | 14700 |
9/1/08 | 5788.64 | 14205.02 | 28935.74 | 25300 | 10547.3 | 65000 | 69500 | 36000.2 | 24200 | 17000 |
10/1/08 | 4532.49 | 11033.03 | 24254.92 | 19000 | 9747.51 | 58000 | 63000 | 28571.59 | 19000 | 14900 |
11/1/08 | 3030.3 | 10113.61 | 20314.09 | 18000 | 6998.21 | 45200 | 35300 | 14742.94 | 13500 | 11000 |
12/1/08 | 2331 | 9332.1 | 19610.37 | 16200 | 6498.34 | 36200 | 30400 | 18400.11 | 13500 | 9800 |
1/1/09 | 2175.6 | 8458.65 | 17884.61 | 16000 | 6998.21 | 34500 | 30400 | 13942.94 | 11700 | 8200 |
2/1/09 | 2201.5 | 7861.03 | 17820.51 | 15700 | 7298.13 | 33000 | 31000 | 13028.64 | 12200 | 7700 |
3/1/09 | 2292.15 | 6987.58 | 16025.65 | 12100 | 6648.3 | 28300 | 24000 | 11085.78 | 9700 | 6700 |
4/1/09 | 3082.1 | 7769.09 | 19230.77 | 12400 | 6748.27 | 33100 | 31900 | 14857.23 | 11800 | 9400 |
5/1/09 | 3613.04 | 9653.9 | 23717.96 | 13100 | 8247.89 | 36500 | 31800 | 14628.66 | 11300 | 10500 |
6/1/09 | 7834.73 | 12779.93 | 28461.55 | 15700 | 11946.95 | 44200 | 46700 | 21200.12 | 13300 | 15600 |
7/1/09 | 10036.23 | 15262.36 | 29871.81 | 15600 | 14596.27 | 40500 | 40500 | 19028.68 | 11900 | 13300 |
8/1/09 | 11007.48 | 16687.45 | 29871.81 | 16000 | 14896.19 | 47400 | 45800 | 20171.55 | 12200 | 14200 |
9/1/09 | 13338.47 | 18020.61 | 30576.94 | 19200 | 13746.48 | 49700 | 62000 | 25142.99 | 13200 | 16300 |
10/1/09 | 21885.47 | 18564.48 | 29358.98 | 20600 | 13996.42 | 47100 | 60000 | 25428.71 | 13000 | 16300 |
11/1/09 | 19813.47 | 16955.17 | 27564.11 | 29400 | 14996.16 | 42200 | 57000 | 24685.86 | 13300 | 16800 |
12/1/09 | 16316.98 | 15288.41 | 25287.41 | 24800 | 14946.18 | 38100 | 61000 | 23371.55 | 11700 | 15800 |
1/1/10 | 15798.97 | 13851.52 | 24182.3 | 26900 | 14646.25 | 34000 | 58000 | 21371.55 | 11300 | 14300 |
Since you don't show a variable or anything labeled as "Rt" you need to describe in terms of you data what exactly is meant by "(Rt/Rt-1) -1 ".
You probably need to reshape your data so that you have something that looks more like:
Name Code Date Value
Do you have a SAS data set? That looks very improbable for the sort of calculation I think you intend as all of those values under the names are going to be character.
Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.