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

Hi all,

I am trying to rank transform a data by year (i.e., rank transformation within cross-section) such that the rank transformed that has a mean of 0 and a standard deviation of 1. 

Here is what I have so far, but I am not able to force proc transform to rank transform the data so that SAS rank transforms the data by having mean=0 and std=1:

 

 

data work.have;
 infile cards expandtabs truncover;
 input id year value;
 cards;
 1 2001 0.4
 2 2001 0.3
 3 2001 0.4
 4 2001 0.8
 5 2001 0.9
 6 2001 0.8
 7 2001 0.8
 1 2002 0.3
 2 2002 0.9
 3 2002 0.1
 4 2002 0.9
 5 2002 0.2
 6 2002 0.9
 7 2002 0.9
;
run;

/*    I want to rank the variable "value" by year such that it has a mean=0 and a std=1 			    */
/*    Please note that the rank needs to be performed within each year (i.e., within the cross-seciton) */
proc rank data=work.have out=work.have_ranked;
   by year;
   var value;
   ranks valueRank;
run;

/* 	Results */ 
proc sql;
	create table work.want as 
	select distinct year, mean(value) as average, std(value) as standard_dev
	from work.have_ranked
	group by year;
quit;
	

 

 

Any suggestions will be appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

@Yegen wrote:

Hi all,

I am trying to rank transform a data by year (i.e., rank transformation within cross-section) such that the rank transformed that has a mean of 0 and a standard deviation of 1. 

Here is what I have so far, but I am not able to force proc transform to rank transform the data so that SAS rank transforms the data by having mean=0 and std=1:

 

 

data work.have;
 infile cards expandtabs truncover;
 input id year value;
 cards;
 1 2001 0.4
 2 2001 0.3
 3 2001 0.4
 4 2001 0.8
 5 2001 0.9
 6 2001 0.8
 7 2001 0.8
 1 2002 0.3
 2 2002 0.9
 3 2002 0.1
 4 2002 0.9
 5 2002 0.2
 6 2002 0.9
 7 2002 0.9
;
run;

/*    I want to rank the variable "value" by year such that it has a mean=0 and a std=1 			    */
/*    Please note that the rank needs to be performed within each year (i.e., within the cross-seciton) */
proc rank data=work.have out=work.have_ranked;
   by year;
   var value;
   ranks valueRank;
run;

/* 	Results */ 
proc sql;
	create table work.want as 
	select distinct year, mean(value) as average, std(value) as standard_dev
	from work.have_ranked
	group by year;
quit;
	

 

 

Any suggestions will be appreciated!


PROC RANK gives you unstandardized ranks, not what you want, hence the next step. Use PROC STDIZE to transform the rank numbers so that they have mean 0 and variance 1.

--
Paige Miller

View solution in original post

7 REPLIES 7
PaigeMiller
Diamond | Level 26

@Yegen wrote:

Hi all,

I am trying to rank transform a data by year (i.e., rank transformation within cross-section) such that the rank transformed that has a mean of 0 and a standard deviation of 1. 

Here is what I have so far, but I am not able to force proc transform to rank transform the data so that SAS rank transforms the data by having mean=0 and std=1:

 

 

data work.have;
 infile cards expandtabs truncover;
 input id year value;
 cards;
 1 2001 0.4
 2 2001 0.3
 3 2001 0.4
 4 2001 0.8
 5 2001 0.9
 6 2001 0.8
 7 2001 0.8
 1 2002 0.3
 2 2002 0.9
 3 2002 0.1
 4 2002 0.9
 5 2002 0.2
 6 2002 0.9
 7 2002 0.9
;
run;

/*    I want to rank the variable "value" by year such that it has a mean=0 and a std=1 			    */
/*    Please note that the rank needs to be performed within each year (i.e., within the cross-seciton) */
proc rank data=work.have out=work.have_ranked;
   by year;
   var value;
   ranks valueRank;
run;

/* 	Results */ 
proc sql;
	create table work.want as 
	select distinct year, mean(value) as average, std(value) as standard_dev
	from work.have_ranked
	group by year;
quit;
	

 

 

Any suggestions will be appreciated!


PROC RANK gives you unstandardized ranks, not what you want, hence the next step. Use PROC STDIZE to transform the rank numbers so that they have mean 0 and variance 1.

--
Paige Miller
Rick_SAS
SAS Super FREQ

Ranks of unique values will be integers 1, 2, 3, ....

 

It sounds like you want to standardize the values to have mean=0 and std=1. You can do that with PROC STDIZE:

 


/*    I want to rank the variable "value" by year such that it has a mean=0 and a std=1 			    */
/*    Please note that the rank needs to be performed within each year (i.e., within the cross-seciton) */
proc stdize data=work.have out=work.have_ranked;
   by year;
   var value;
run;

proc means data=have_ranked;
class year;
var value;
run;

proc print data=have_ranked;
run;
Yegen
Pyrite | Level 9

Thanks for your helpful replies, @Rick_SAS and @PaigeMiller. I am trying to follow a prior study that claims that they were using a normalized (to have a mean of 0 as well as a unit standard deviation) rank-transformation. Since they were not explicitly mentioning that they standardized the data to have a zero mean and a unit std, I am not sure if your suggestion @Rick_SAS would produce the same as a normalized rank-transformation?

Yegen
Pyrite | Level 9

@Rick_SAS and @PaigeMiller, I have tried to use both of your suggestions and the results seem to be similar although the transformed values are different:

data work.have;
 infile cards expandtabs truncover;
 input id year value;
 cards;
 1 2001 0.4
 2 2001 0.3
 3 2001 0.4
 4 2001 0.8
 5 2001 0.9
 6 2001 0.8
 7 2001 0.8
 1 2002 0.3
 2 2002 0.9
 3 2002 0.1
 4 2002 0.9
 5 2002 0.2
 6 2002 0.9
 7 2002 0.9
;
run;

/*    I want to rank the variable "value" by year such that it has a mean=0 and a std=1 			    */
/*    Please note that the rank needs to be performed within each year (i.e., within the cross-seciton) */
proc rank data=work.have out=work.have_ranked;
   by year;
   var value;
   ranks valueRank;
run;

PROC STANDARD DATA=have_ranked MEAN=0 STD=1 OUT=have_ranked1;
	by year;
	VAR valueRank value;
RUN;

proc means data=have_ranked1;
class year;
var value valueRank;
run;

proc print data=have_ranked1;
run;


I wanted to see if the rank tranformed values will be similar to the non-rank transformed values after standardizing the data. They seem pretty similar, but not exactly the same.  

Reeza
Super User
Does your data have ties?
PaigeMiller
Diamond | Level 26

@Yegen wrote:

@Rick_SAS and @PaigeMiller, I have tried to use both of your suggestions and the results seem to be similar although the transformed values are different:

I wanted to see if the rank tranformed values will be similar to the non-rank transformed values after standardizing the data. They seem pretty similar, but not exactly the same.  


Pretty similar, but not exactly the same? I don't know what that means, you need to show us what you did and what the different results are.

 

From what I can see, @Rick_SAS and my suggestion are the same, and should produce the same results.

--
Paige Miller
Yegen
Pyrite | Level 9

@PaigeMiller, I think @Rick_SAS suggested that I should use directly the variable "value" in the standardization rather than using the "valueRank" variable. When I use the "value" and "valueRank" variables in the standardization, I get different "standardized values". 

@Reeza, there was no mentioning in the paper that I am following whether they have adjusted for ties. So I assume no adjustments were made there..  

Ready to join fellow brilliant minds for the SAS Hackathon?

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!
What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 7 replies
  • 1737 views
  • 4 likes
  • 4 in conversation