BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Agent1592
Pyrite | Level 9

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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;

or

 

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.

 

View solution in original post

4 REPLIES 4
Reeza
Super User

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

Agent1592
Pyrite | Level 9

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.

Reeza
Super User

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 😉

ballardw
Super User

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;

or

 

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.

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 2208 views
  • 1 like
  • 3 in conversation