topic SD of data and then ranking that data in SAS Programming

SD of data and then ranking that data

starkey09 — Mon, 26 Nov 2018 08:40:43 GMT

Hi,

I have a data set that I am struggling to write the code for analysing. I have a list of companies and their % ret every month from 2005 onward. I need to work out the standard deviation for the preceding 12 months every quarter and then rank that data each quarter creating a new column that shows a number of the rank from lowest to highest. How would I go about doing this? I have copied a snippet of the data set below.

Cheers

NAME	1/01/2005	1/02/2005	1/03/2005	1/04/2005	1/05/2005	1/06/2005	1/07/2005	1/08/2005	1/09/2005	1/10/2005	1/11/2005
ABANO HEALTHCARE GROUP		5.882%	8.333%	-12.821%	0.000%	38.235%	10.638%	25.000%	3.077%	-2.985%	-12.308%
ABDN.NEW DAWN IT. (NZE)
ACURITY HEALTH GROUP		6.644%	-1.297%	1.314%	-4.151%	11.108%	9.698%	15.911%	4.903%	28.596%	-9.885%
AFC GROUP HOLDINGS
AFFCO HOLDINGS		4.881%	6.977%	-4.350%	-18.178%	8.333%	15.385%	6.667%	0.000%
AFFCO HOLDINGS RTS.
AFT PHARMACEUTICALS
AIR NEW ZEALAND		0.000%	-0.609%	-6.128%	-6.610%	3.582%	-2.067%	-9.130%	-5.450%	-3.459%	-4.309%
AIR NEW ZEALAND 'B'
AIRWORK HOLDINGS
AKRON CORP.
ALLFLEX HDG.
ALLIED FARMERS		-3.848%	6.001%	9.809%	8.771%	-8.064%	-2.458%	-1.796%	-6.594%	0.979%	-1.213%

Re: SD of data and then ranking that data

PaigeMiller — Mon, 26 Nov 2018 13:08:54 GMT

Could you provide us with valid SAS variable names? 1/01/2005 is not a valid SAS variable name.

In fact, could you please provide a portion of your data as SAS data step code, as explained here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Regarding the data you show, there are not 12 months of data so it would be difficult for us to write a program to do this computation. Perhaps a larger set of data would be useful.

And also could you please explain this via example? "I need to work out the standard deviation for the preceding 12 months every quarter"

Re: SD of data and then ranking that data

starkey09 — Tue, 27 Nov 2018 04:40:32 GMT

Thanks Paige for your reply. In regards to the SAS variable name- do I need to change it to 01Jan2005? Is there an easy way to do this or do I amend them in excel before uploading. Sorry if this is basic, I am extremely new to SAS and doing my best to learn as quick as possible.

Do I need to do the above before I provide a portion of the data?

What I need to do is take the standard deviation for the monthly returns of each stock for the preceding 12 months and re-measure this every quarter. For example, I would take the SD for the monthly returns from 01/01/2005-01/12/2005 (DD/MM/YYYY), these would be ranked lowest at the top. Then I would do the same thing again but 3 months later, so the SD would be calculated from 01/04/2005-01/03/2006.

Thanks for your help, it is very much appreciated.

Re: SD of data and then ranking that data

PaigeMiller — Tue, 27 Nov 2018 13:01:15 GMT

Essentially, you have poorly structured and poorly named data, and there is no obvious and easy method of getting from where you are to what you want, as far as I can see.

For maximum efficiency in SAS, you would want your data structured like this:

NAME             DATE               NET

ABANO           2/1/2005          5.882
ABANO           3/1/2005          8.333
...
ACURITY         2/1/2005          6.644

etc.

How to get there is not easy. This would require lots of SAS programming to take the Excel file and obtain a workable data file where you can then compute 12month standard deviations.

My advice is to find some SAS expert at your company or university who can help you with this. Or maybe someone here has an idea. At this time, I do not have a good idea about how to proceed in SAS.

It might be much easier to do this in Excel.