Ranking by State
in [SAS]
|
From: Sdlentertd on 2 Mar 2010 18:14 I need help figuring out how to rank my results by state.For each color what is the rank(based on SumVal) for all states .. I have this SAS dataset State sumval Color AK 10 Red AK 12 Blue AK 7 Green AZ 3 Yellow AZ 5 Red AZ 20 Blue NY 2 Green etc... The output will list all states with Rankings by Color : Red State SumVal Rank AK 10 1 AZ 5 2 NY 0 . Blue State SumVal Rank AK 12 2 AZ 20 1 NY 0 . Green State SumVal Rank AK 0 . AZ 3 1 NY 0 . Green State SumVal Rank AK 7 1 AZ 0 . NY 2 2 So one report for each of Colors (I have 50 or so) and I need to list all 50 states even if the value is 0, and if the value is zero that the rank should be missing. Thank you
From: Sdlentertd on 2 Mar 2010 18:41 On Mar 2, 4:14 pm, Sdlentertd <sdlente...(a)gmail.com> wrote: > I need help figuring out how to rank my results by state.For each > color what is the rank(based on SumVal) for all states .. I have this > SAS dataset > > State sumval Color > AK 10 Red > AK 12 Blue > AK 7 Green > AZ 3 Yellow > AZ 5 Red > AZ 20 Blue > NY 2 Green > > etc... > The output will list all states with Rankings by Color : > > Red > State SumVal Rank > AK 10 1 > AZ 5 2 > NY 0 . > > Blue > State SumVal Rank > AK 12 2 > AZ 20 1 > NY 0 . > > Green > State SumVal Rank > AK 0 . > AZ 3 1 > NY 0 . > > Green > State SumVal Rank > AK 7 1 > AZ 0 . > NY 2 2 > > So one report for each of Colors (I have 50 or so) > and I need to list all 50 states even if the value is 0, and if the > value is zero that the rank should be missing. > Thank you I came up with this code, but it doesn't list all states, only populated ones. proc rank data=have out=new descending ties = low ; by color; var SumVal ; run;
From: Arthur Tabachneck on 2 Mar 2010 19:10 I really should just wait until data_null_ shows us the direct way to do what you want but, in case you need it right away, I think that the following accomplishes the task: data have; input State $ sumval Color $; cards; AK 10 Red AK 12 Blue AK 7 Green AZ 3 Yellow AZ 5 Red AZ 20 Blue NY 2 Green ; proc sort data=have; by color state; run; proc sql noprint; create table need as select unique l.state, r.color from have as l, have as r order by color,state; quit; data need; merge have need; by color state; if missing(sumval) then sumval=0; run; proc sort data=need; by color; run; proc rank data=need descending out=want; by color; var sumval; ranks rank; run; proc sort data=want; by color rank state; run; data want; set want; if sumval eq 0 then call missing(rank); run; proc print data=want; run; HTH, Art ------------ On Mar 2, 6:41 pm, Sdlentertd <sdlente...(a)gmail.com> wrote: > On Mar 2, 4:14 pm, Sdlentertd <sdlente...(a)gmail.com> wrote: > > > > > > > I need help figuring out how to rank my results by state.For each > > color what is the rank(based on SumVal) for all states .. I have this > > SAS dataset > > > State sumval Color > > AK 10 Red > > AK 12 Blue > > AK 7 Green > > AZ 3 Yellow > > AZ 5 Red > > AZ 20 Blue > > NY 2 Green > > > etc... > > The output will list all states with Rankings by Color : > > > Red > > State SumVal Rank > > AK 10 1 > > AZ 5 2 > > NY 0 . > > > Blue > > State SumVal Rank > > AK 12 2 > > AZ 20 1 > > NY 0 . > > > Green > > State SumVal Rank > > AK 0 . > > AZ 3 1 > > NY 0 . > > > Green > > State SumVal Rank > > AK 7 1 > > AZ 0 . > > NY 2 2 > > > So one report for each of Colors (I have 50 or so) > > and I need to list all 50 states even if the value is 0, and if the > > value is zero that the rank should be missing. > > Thank you > > I came up with this code, but it doesn't list all states, only > populated ones. > > proc rank data=have out=new descending ties = low ; > by color; > > var SumVal ; > > run;- Hide quoted text - > > - Show quoted text -
From: Mike Zdeb on 2 Mar 2010 20:45 hi ... here's another idea ... data x; input state : $2. sumval color : $6.; datalines; AK 10 Red AK 12 Blue AK 7 Green AZ 3 Yellow AZ 5 Red AZ 20 Blue NY 2 Green ; run; proc summary data=x nway completetypes; class color state; var sumval; output out=y (drop=_:) sum=; run; proc rank data=y out=y; var sumval; by color; ranks rank; run; proc print data=y noobs; by color; run; output ... COLOR=Blue state sumval rank AK 12 1 AZ 20 2 NY . . COLOR=Green state sumval rank AK 7 2 AZ . . NY 2 1 COLOR=Red state sumval rank AK 10 2 AZ 5 1 NY . . COLOR=Yellow state sumval rank AK . . AZ 3 1 NY . . -- Mike Zdeb U(a)Albany School of Public Health One University Place Rensselaer, New York 12144-3456 P/518-402-6479 F/630-604-1475 > I really should just wait until data_null_ shows us the direct way to > do what you want but, in case you need it right away, I think that the > following accomplishes the task: > > data have; > input State $ sumval Color $; > cards; > AK 10 Red > AK 12 Blue > AK 7 Green > AZ 3 Yellow > AZ 5 Red > AZ 20 Blue > NY 2 Green > ; > > proc sort data=have; > by color state; > run; > > proc sql noprint; > create table need as > select unique l.state, > r.color > from have as l, have as r > order by color,state; > quit; > > data need; > merge have need; > by color state; > if missing(sumval) then sumval=0; > run; > > proc sort data=need; > by color; > run; > > proc rank data=need descending out=want; > by color; > var sumval; > ranks rank; > run; > > proc sort data=want; > by color rank state; > run; > > data want; > set want; > if sumval eq 0 then call missing(rank); > run; > > proc print data=want; > run; > > HTH, > Art > ------------ > On Mar 2, 6:41 pm, Sdlentertd <sdlente...(a)gmail.com> wrote: >> On Mar 2, 4:14 pm, Sdlentertd <sdlente...(a)gmail.com> wrote: >> >> >> >> >> >> > I need help figuring out how to rank my results by state.For each >> > color what is the rank(based on SumVal) for all states .. I have this >> > SAS dataset >> >> > State sumval Color >> > AK 10 Red >> > AK 12 Blue >> > AK 7 Green >> > AZ 3 Yellow >> > AZ 5 Red >> > AZ 20 Blue >> > NY 2 Green >> >> > etc... >> > The output will list all states with Rankings by Color : >> >> > Red >> > State SumVal Rank >> > AK 10 1 >> > AZ 5 2 >> > NY 0 . >> >> > Blue >> > State SumVal Rank >> > AK 12 2 >> > AZ 20 1 >> > NY 0 . >> >> > Green >> > State SumVal Rank >> > AK 0 . >> > AZ 3 1 >> > NY 0 . >> >> > Green >> > State SumVal Rank >> > AK 7 1 >> > AZ 0 . >> > NY 2 2 >> >> > So one report for each of Colors (I have 50 or so) >> > and I need to list all 50 states even if the value is 0, and if the >> > value is zero that the rank should be missing. >> > Thank you >> >> I came up with this code, but it doesn't list all states, only >> populated ones. >> >> proc rank data=have out=new descending ties = low ; >> by color; >> >> var SumVal ; >> >> run;- Hide quoted text - >> >> - Show quoted text - >
From: Mike Zdeb on 2 Mar 2010 20:54
hi ... didn't notice descending order of rank ... just use stuff in last posting below and add descending ... proc rank data=y out=y descending; var sumval; by color; ranks rank; run; ps if you really want those missing values of sumval to be 0 use a data step after PROC RANK ... proc summary data=x nway completetypes; class color state; var sumval; output out=y (drop=_:) sum=; run; proc rank data=y out=y descending; var sumval; by color; ranks rank; run; data y; set y; sumval = sum(sumval,0); run; -- Mike Zdeb U(a)Albany School of Public Health One University Place Rensselaer, New York 12144-3456 P/518-402-6479 F/630-604-1475 > hi ... here's another idea ... > > data x; > input state : $2. sumval color : $6.; > datalines; > AK 10 Red > AK 12 Blue > AK 7 Green > AZ 3 Yellow > AZ 5 Red > AZ 20 Blue > NY 2 Green > ; > run; > > proc summary data=x nway completetypes; > class color state; > var sumval; > output out=y (drop=_:) sum=; > run; > > proc rank data=y out=y; > var sumval; > by color; > ranks rank; > run; > > proc print data=y noobs; > by color; > run; > > output ... > > COLOR=Blue > state sumval rank > AK 12 1 > AZ 20 2 > NY . . > > > COLOR=Green > state sumval rank > AK 7 2 > AZ . . > NY 2 1 > > > COLOR=Red > state sumval rank > AK 10 2 > AZ 5 1 > NY . . > > > COLOR=Yellow > state sumval rank > AK . . > AZ 3 1 > NY . . > > > > -- > Mike Zdeb > U(a)Albany School of Public Health > One University Place > Rensselaer, New York 12144-3456 > P/518-402-6479 F/630-604-1475 > >> I really should just wait until data_null_ shows us the direct way to >> do what you want but, in case you need it right away, I think that the >> following accomplishes the task: >> >> data have; >> input State $ sumval Color $; >> cards; >> AK 10 Red >> AK 12 Blue >> AK 7 Green >> AZ 3 Yellow >> AZ 5 Red >> AZ 20 Blue >> NY 2 Green >> ; >> >> proc sort data=have; >> by color state; >> run; >> >> proc sql noprint; >> create table need as >> select unique l.state, >> r.color >> from have as l, have as r >> order by color,state; >> quit; >> >> data need; >> merge have need; >> by color state; >> if missing(sumval) then sumval=0; >> run; >> >> proc sort data=need; >> by color; >> run; >> >> proc rank data=need descending out=want; >> by color; >> var sumval; >> ranks rank; >> run; >> >> proc sort data=want; >> by color rank state; >> run; >> >> data want; >> set want; >> if sumval eq 0 then call missing(rank); >> run; >> >> proc print data=want; >> run; >> >> HTH, >> Art >> ------------ >> On Mar 2, 6:41 pm, Sdlentertd <sdlente...(a)gmail.com> wrote: >>> On Mar 2, 4:14 pm, Sdlentertd <sdlente...(a)gmail.com> wrote: >>> >>> >>> >>> >>> >>> > I need help figuring out how to rank my results by state.For each >>> > color what is the rank(based on SumVal) for all states .. I have this >>> > SAS dataset >>> >>> > State sumval Color >>> > AK 10 Red >>> > AK 12 Blue >>> > AK 7 Green >>> > AZ 3 Yellow >>> > AZ 5 Red >>> > AZ 20 Blue >>> > NY 2 Green >>> >>> > etc... >>> > The output will list all states with Rankings by Color : >>> >>> > Red >>> > State SumVal Rank >>> > AK 10 1 >>> > AZ 5 2 >>> > NY 0 . >>> >>> > Blue >>> > State SumVal Rank >>> > AK 12 2 >>> > AZ 20 1 >>> > NY 0 . >>> >>> > Green >>> > State SumVal Rank >>> > AK 0 . >>> > AZ 3 1 >>> > NY 0 . >>> >>> > Green >>> > State SumVal Rank >>> > AK 7 1 >>> > AZ 0 . >>> > NY 2 2 >>> >>> > So one report for each of Colors (I have 50 or so) >>> > and I need to list all 50 states even if the value is 0, and if the >>> > value is zero that the rank should be missing. >>> > Thank you >>> >>> I came up with this code, but it doesn't list all states, only >>> populated ones. >>> >>> proc rank data=have out=new descending ties = low ; >>> by color; >>> >>> var SumVal ; >>> >>> run;- Hide quoted text - >>> >>> - Show quoted text - >> > |