topic Re: Proc Rank quintiles but by year in SAS Programming

Proc Rank quintiles but by year

ksmith133333333 — Sun, 29 Dec 2019 00:13:18 GMT

Hi first time poster, let me know if i need to provide more details.

I am attempting to use proc rank to create quintiles for 4 different variables.

My data is firm-year observations from 2010-2017. I have four variables i need to separately assign quintiles to.

First: Is there a way to have the proc rank consider the year in assigning the quintiles? So it calculates a 2010 set of quintiles and assigns those for the 2010 firm-years, 2011, etc.?

Second: Is there a way to have proc rank ignore missing values in creating the quintiles?

{
data WORK.MERGERS101;
infile datalines dsd truncover;
input EIN:BEST12. NAME:$35. FISYR:BEST12. relativeequity:32. revenueconcentration:32. adminratio:32. operatingmargin:32. dissolver:32.;
format EIN BEST12. FISYR BEST12.;
datalines;
990107223 ACADEMY OF THE PACIFIC 2012 1.3208732899 0.312010507 0.1667243535 -0.005034895 1
980365458 MOBILE MEMBER CARE TEAM 2016 1.3077040195 0.4397492632 . -0.126948843 1
990297201 Elementary School Center Hawaii 2010 293.79565217 0 . -13.73478261 1
990345193 Moolelo Performing Arts Company 2015 0.3496993545 0.5470906723 . -2.045450526 1
20481994 Warde Health Center Inc 2014 0.0657027419 0.9282159016 0.1512901232 0.0301761733 1
;;;;
}

Re: Proc Rank quintiles but by year

mkeintz — Sun, 29 Dec 2019 03:51:57 GMT

Are your data already sorted by year? For instance below is dataset sashelp.cars already sorted by ORIGIN (ASIA EUROPE USA). You can use PROC RANK with a BY statements for all the variables of interest.

proc sort data=sashelp.cars out=have;
  by origin;
run;
proc rank data=have out=want   groups=5 ;
  by origin;
  var    Horsepower          Invoice          Length          MSRP          Weight;
  ranks  Horsepower_quintile Invoice_quintile Length_quintile MSRP_quintile Weight_quintile;
run;

For your dataset, just substitute YEAR for origin and your variables for horsepower--weight. But remember the quintile variables will be valued at 0 through 4, not 1 through 5. You may want to run a subsequent data step to add 1 to the quintile variables

Re: Proc Rank quintiles but by year

ksmith133333333 — Sun, 29 Dec 2019 03:57:26 GMT

It's currently sorted by ein but I can sort it by year. I'll give this a try!