Ranking by State - comp.soft-sys.sas

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

On Mar 2, 4:14=A0pm, Sdlentertd <sdlente...@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) =A0for all states .. I have this > SAS dataset > > State =A0sumval =A0 =A0 Color > AK =A0 =A0 =A010 =A0 =A0 =A0 =A0 =A0 Red > AK =A0 =A0 =A012 =A0 =A0 =A0 =A0 =A0 Blue > AK =A0 =A0 =A07 =A0 =A0 =A0 =A0 =A0 =A0 =A0Green > AZ =A0 =A0 =A0 3 =A0 =A0 =A0 =A0 =A0 =A0 Yellow > AZ =A0 =A0 =A05 =A0 =A0 =A0 =A0 =A0 =A0 =A0Red > AZ =A0 =A0 20 =A0 =A0 =A0 =A0 =A0 =A0 Blue > NY =A0 =A0 2 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Green > > etc... > The output will list all states with Rankings =A0by Color : > > Red > State =A0 =A0SumVal =A0 =A0 Rank > AK =A0 =A0 =A0 10 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A01 > AZ =A0 =A0 =A0 =A0 5 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02 > NY =A0 =A0 =A0 =A00 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 . > > Blue > State =A0 =A0SumVal =A0 =A0 Rank > AK =A0 =A0 =A0 12 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02 > AZ =A0 =A0 =A0 =A0 20 =A0 =A0 =A0 =A0 =A0 =A0 =A0 1 > NY =A0 =A0 =A0 =A00 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 . > > Green > State =A0 =A0SumVal =A0 =A0 Rank > AK =A0 =A0 =A0 0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 . > AZ =A0 =A0 =A0 =A0 3 =A0 =A0 =A0 =A0 =A0 =A0 =A0 1 > NY =A0 =A0 =A0 =A00 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0. > > Green > State =A0 =A0SumVal =A0 =A0 Rank > AK =A0 =A0 =A0 7 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A01 > AZ =A0 =A0 =A0 =A0 0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 . > NY =A0 =A0 =A0 =A02 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02 > > 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=3Dhave out=3Dnew descending ties =3D low ; by color; var SumVal ; run;

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...@gmail.com> wrote: > On Mar 2, 4:14 pm, Sdlentertd <sdlente...@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 -

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@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...@gmail.com> wrote: >> On Mar 2, 4:14 pm, Sdlentertd <sdlente...@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 - >

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@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@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...@gmail.com> wrote: >>> On Mar 2, 4:14 pm, Sdlentertd <sdlente...@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 - >> >

I was late to read this thread. Mike Zdeb has given a solution based on proc summary and proc rank. I was thinking to use hash objects. It looked difficult at first to incorporate all the needs of the OP in a hash solution. It is not. data have; 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; It needs a presorted data set to get the ranks. A hash table is stored based on the keys of color and state. Another hash table is built to save only the unique colors to check the presence in the former hash table to add missing values. proc sort data = have; by color descending sumval; run; data _null_; if _n_ = 1 then do; if 0 then set have; declare hash h(ordered:'y'); h.definekey('color','state'); h.definedata('color','state','sumval','rank'); h.definedone(); declare hash s(ordered:'y'); s.definekey('st'); s.definedone(); do until(z); do until(last.color); set have(keep = state rename=(state = st)) end = z; if s.check() ne 0 then s.add(); end; end; declare hiter hi('s'); end; do until(last); rank = 0; do until(last.color); set have end = last; by color; if h.find() ne 0 then rank + 1; h.replace(); end; ** check all states are inserted; do rc = hi.first() by 0 while(rc = 0); if h.find(key:color,key:st) ne 0 then do; state = st; sumval = 0; rank = .; h.add(); end; rc = hi.next(); end; end; h.output(dataset:'need'); stop; run; proc print data = need noobs; run; The output of dataset: color state sumval rank Blue AK 12 2 Blue AZ 20 1 Blue NY 0 . Green AK 7 1 Green AZ 0 . Green NY 2 2 Red AK 10 1 Red AZ 5 2 Red NY 0 . Yellow AK 0 . Yellow AZ 3 1 Yellow NY 0 . Muthia Kachirayan On Tue, Mar 2, 2010 at 7:14 PM, Sdlentertd <sdlentertd@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 >

It is not unique colors but state. On Thu, Mar 4, 2010 at 12:40 PM, Muthia Kachirayan < muthia.kachirayan@gmail.com> wrote: > I was late to read this thread. Mike Zdeb has given a solution based on > proc summary and proc rank. I was thinking to use hash objects. It looked > difficult at first to incorporate all the needs of the OP in a hash > solution. It is not. > > > data have; > > 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; > > It needs a presorted data set to get the ranks. A hash table is stored > based on the keys of color and state. Another hash table is built to save > only the unique colors to check the presence in the former hash table to add > missing values. > > > proc sort data = have; > by color descending sumval; > run; > > data _null_; > if _n_ = 1 then do; > > if 0 then set have; > > declare hash h(ordered:'y'); > h.definekey('color','state'); > h.definedata('color','state','sumval','rank'); > h.definedone(); > > declare hash s(ordered:'y'); > s.definekey('st'); > s.definedone(); > > do until(z); > do until(last.color); > set have(keep = state rename=(state = st)) end = z; > if s.check() ne 0 then s.add(); > end; > end; > > declare hiter hi('s'); > end; > > do until(last); > rank = 0; > do until(last.color); > set have end = last; > by color; > if h.find() ne 0 then rank + 1; > h.replace(); > end; > > ** check all states are inserted; > > do rc = hi.first() by 0 while(rc = 0); > if h.find(key:color,key:st) ne 0 then do; > state = st; > sumval = 0; > rank = .; > h.add(); > end; > rc = hi.next(); > end; > end; > h.output(dataset:'need'); > stop; > run; > > proc print data = need noobs; > run; > > The output of dataset: > > > color state sumval rank > > Blue AK 12 2 > Blue AZ 20 1 > Blue NY 0 . > Green AK 7 1 > Green AZ 0 . > Green NY 2 2 > Red AK 10 1 > Red AZ 5 2 > Red NY 0 . > Yellow AK 0 . > Yellow AZ 3 1 > Yellow NY 0 . > > > Muthia Kachirayan > > On Tue, Mar 2, 2010 at 7:14 PM, Sdlentertd <sdlentertd@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 >> > >

Similiar Articles:

How to generate proper seeds for the rand function in Monte Carlo ...
The 'state' generator is not > recommended, having been functionally replaced by ... I want to simulate artificial data ranking 15 criteria (C1, .... , C15). So, each ...

remote printing blank pages - comp.sys.sun.admin
... lpstat -lp dprint printer daver unknown state. enabled since Oct 15 15:12 2007. available. Remote Name: dprint Remote Server: dprint_ps Rank Job Id ...

Re: remote printer prints blank pages - comp.sys.sun.admin ...
... lpstat -lp dprint > printer daver unknown state. enabled since Oct 15 15:12 2007. available. > Remote Name: dprint > Remote Server: dprint_ps > > Rank Job ...

Poker hand evaluator - comp.lang.javascript
And it would be a further bonus if the evaluator could retain most of its state when ... >> (with of course a similar table creation for the 6175 different rank >> counts on ...

proc power question for McNemar test - comp.soft-sys.sas ...
Thanks in advance! Robert Feyerharm Oklahoma State Department of Health ... power question for McNemar test - comp.soft-sys.sas ... proc life test, log rank ...

Regulator for Spartan 2 - comp.arch.fpga
What bypass capacitor ratings would be OK for my design .01uf would be OK? ... If so, I guess there is some state machine in there (scary...), if not then I'm sure ...

All Solutions Manuals & Test Banks Are HERE !!! #5 - comp.unix ...
... 10th Edition, Harrigan, Nice, Instructor Manual Politics and Policy in States and ... Principles and Practices, 6th Edition, Holechek, Pieper, Herbel, Test Bank Ranking ...

Test Banks & Solutions Manual - comp.lang.java.programmer ...
... and Practices, 5th Edition, Holechek, Pieper, Herbel, Instructor Manual Ranking Task ... Out With Visual Basic 2008 Update, 4th Edition, Gaddis, Irvine, Test Bank State ...

statistical t test to compare two groups - comp.soft-sys.matlab ...
[S-Function in Simulink/Matlab Error!!!] has continuous states ... Dear All, I am not ... groups using the logrank test. ... different between the two procedures, as the rank ...

top 10 uses for random data compression?? anyone? - comp ...
Try musting the conference's communist allocation and Brion will state you! While breeds even approve backs, the reds often bow following the blunt frauds.

State Rankings - The 2012 Statistical Abstract - U.S. Census Bureau
US Census Bureau The 2012 Statistical Abstract: State Rankings

The Rankings - United Health Foundation
America's Health Rankings® — 2011 edition shows Vermont at the top of the list of healthiest states again this year. The state has steadily risen in the rankings ...

7/21/2012 6:19:43 PM