```Date: Sun, 31 Dec 2006 10:51:09 -0500 Reply-To: "Howard Schreier " Sender: "SAS(r) Discussion" From: "Howard Schreier " Subject: Re: Comparison across columns?? On Sat, 30 Dec 2006 18:26:05 -0500, Margaret Asmani wrote: >Thanks very much everybody for the very useful suggestions. > >I use Florio's code as it's convenient not having to list the variables >again considering that I have a large number. A follow-up question: is it >possible to compute frequencies only for 1, i.e. the lowest values only? > >Happy New Year!! > >Margaret Here's a variation on Florio's solution which should give you that flexibility. data have; input A B C D E; cards; 25 32 65 32 89 65 32 47 89 96 41 52 63 96 45 12 45 65 98 78 45 56 89 78 63 45 89 56 89 56 56 89 56 45 78 45 56 56 78 89 12 45 98 65 78 12 12 32 35 56 ; Generate row numbers: data numbered; set have; rownum + 1; run; Transpose, but into a normalized structure: proc transpose data = numbered out = long(where = (_name_^='rownum') ); by rownum; var _numeric_; run; Compute ranks: proc rank data = long out=ranked ties=low; by rownum; var col1; ranks Rank; run; Derive frequencies for entire set: proc freq data = ranked; tables _name_*rank / list nocum nopercent; run; Result: _NAME_ Rank Frequency --------------------------- A 1 8 A 2 1 A 3 1 B 1 2 B 2 5 B 3 1 B 4 1 B 5 1 C 2 4 C 3 2 C 4 2 C 5 2 D 1 1 D 2 1 D 3 1 D 4 5 D 5 2 E 2 2 E 3 1 E 4 3 E 5 4 To restrict output, just include the requisite WHERE statement: proc freq data = ranked; where rank=1; tables _name_*rank / list nocum nopercent; run; Result: _NAME_ Rank Frequency --------------------------- A 1 8 B 1 2 D 1 1 On Fri, 29 Dec 2006 17:31:10 -0500, Florio Arguillas wrote: >Hi Margaret, > >Try this code. This takes into consideration the ties in your data set. > >The macro variable t in this code returns the total number of >observations. If you do not want to use macros in your code, simply >replace the &t's with the total number of observations in your >dataset (in your example it is 10), and delete the call symput statement. > >The code first transposes the data to prep it for ranking to >determine which variable has the lowest value, which variable has >the next higher value, the next higher value etc. (as you >requested). Once ranked, the dataset is re-transposed so that its >structure will correspond with your source data set. Once transposed >the source (HAVE) and output (NEED) data sets are merged >(NEEDED). The Freq procedure displays how many times var A to E have >been ranked 1 (lowest) to 5 (highest). Values of 1.5's, 2.5's, 3.5's >and 4.5's indicate ties. > >HTH, > >Florio > > > >data have; >input A B C D E; >call symput ('t', compress(_n_)); >cards; >25 32 65 32 89 >65 32 47 89 96 >41 52 63 96 45 >12 45 65 98 78 >45 56 89 78 63 >45 89 56 89 56 >56 89 56 45 78 >45 56 56 78 89 >12 45 98 65 78 >12 12 32 35 56 >; >run; > >proc transpose data = have out = need; >var a b c d e; >run; > >proc rank data = need out=need; >var col1-col&t; >ranks rcol1-rcol&t; >run; > >proc transpose data = need out = need (drop = _name_ _label_) prefix = RankOf; >var rcol1-rcol&t; >id _name_; >run; > >data needed; > merge have need; >run; > >proc freq data = needed; >table rankofa--rankofe; >run; > > > > >At 02:17 PM 12/29/2006, Margaret Asmani wrote: >>Hi, >> >>I have 20 columns (variables) and want to compare their values. >>For example, >> >>A B C D E >>25 32 65 32 89 >>65 32 47 89 96 >>41 52 63 96 45 >>12 45 65 98 78 >>45 56 89 78 63 >>45 89 56 89 56 >>56 89 56 45 78 >>45 56 56 78 89 >>12 45 98 65 78 >>12 12 32 35 56 >> >>and want to compare the values i.e 25,32,65,32,89 and at the end be able to >>tell how many times variable A had the lowest value, which variable had the >>next higher value, the next higher value etc. >> >>Any suggestions? ```

Back to: Top of message | Previous page | Main SAS-L page