topic Re: Double sorting Portfolios into Quintiles in SAS Programming

Double sorting Portfolios into Quintiles

Agent1592 — Wed, 11 Apr 2018 22:32:28 GMT

Dear SAS Community. I am trying to double sort data into quintiles. First I am sorting monthly on var1. Then I am sorting within each group of var1, var2. Here is my current code. My issue is in the second step I am not sorting just by rvar1 but not by yymm. How can I sort by by rvar1 and yymm in the second step

/*First Step- Sort data into quintiles based on var1*/
proc sort data=have;
      by yymm;
run;
proc rank data=have out=have groups=5;
	by yymm;
	var var1;
	ranks rvar1 ;
	run;
/*Second Step- sort var2 within each quintile of var1 but by yymm
The idea is to avoid look ahead bias*/

proc sort data=have;
      by rvar1;
run;
proc rank data=have out=have groups=5;
	by rvar1;
	var var2;
	ranks rvar2 ;
	run;

Re: Double sorting Portfolios into Quintiles

Reeza — Wed, 11 Apr 2018 22:42:52 GMT

Are you sure those first steps are doing what you expect? Based on your comments, that doesn't seem correct...

Re: Double sorting Portfolios into Quintiles

Agent1592 — Wed, 11 Apr 2018 22:48:22 GMT

I am not sure whether is is correct. Can you tell me the correct code then? The idea of the first step is to sort monthly on var1.

Re: Double sorting Portfolios into Quintiles

Reeza — Wed, 11 Apr 2018 22:56:26 GMT

It's not a matter of code, it's a matter of logic. Your code is syntax wise correct, but I'm not sure if you're stating your logic correctly because the logic doesn't match the code shown...and I can't know which is incorrect because there's no context and it's not my problem.

For example, you say your first step is to sort by VAR1 but in the BY statement in your first sort, it's by YYMM.

/*First Step- Sort data into quintiles based on var1*/
proc sort data=have;
      by yymm;
run;

But if you know what you're trying to do, I would open my data set after the sort and see if it was sorted correctly and the ranks are created accordingly. There isn't anything too difficult in this question so you should be able to guess and test it - it is the scientific way after all 😉

Re: Double sorting Portfolios into Quintiles

ballardw — Wed, 11 Apr 2018 22:58:08 GMT

If I have a clue as to what you want one of these might be what you are looking for:

proc sort data=have;
      by yymm rvar1;
run;
proc rank data=have out=have2 groups=5;
	by yymm rvar1;
	var var2;
	ranks rvar2 ;
run;

proc sort data=have;
      by rvar1 yymm;
run;
proc rank data=have2 out=have groups=5;
	by rvar1 yymm;
	var var2;
	ranks rvar2 ;
run;

if not then time to post some input data and what the result for that data would be.