Ranking by State
in [SAS]
|
From: Muthia Kachirayan on 4 Mar 2010 11:40 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(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 >
From: Muthia Kachirayan on 4 Mar 2010 11:44
It is not unique colors but state. On Thu, Mar 4, 2010 at 12:40 PM, Muthia Kachirayan < muthia.kachirayan(a)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(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 >> > > |