topic Re: Count values one column at a time in SAS Programming

Count values one column at a time

Andre_AA1 — Wed, 12 Jan 2022 21:05:52 GMT

Hi there,

I'm working on a data set with a variable that is ranked according to some criteria, and i'm changing this criteria and comparing the new ranking. So I got a table with the variable being sorted, and its position according to three different rankings. Something like:

data have;
infile datalines;
input OBS$ Rank_1$ Rank_2$ Rank_3$;
datalines;
A	 1	 1	 1
B	 1	 2	 1
C	 2	 2	 3
D	 4	 5	 2
E	 6	 2	 8
F	 2	 7	 4
;
run;

So, A is first position in all three rankings, B is first in Rankings 1 and 3, but second in Ranking 2, etc.

What I want is to count how many items are at each positions per ranking. It would look something like:

data want;
infile datalines;
input Position$ n_Rank_1$ n_Rank_2$ n_Rank_3$;
datalines;
1	 2	 1	 2
2	 2	 3	 1
3	 0	 0	 1
4	 1	 0	 1
5	 0	 1	 0
6	 1	 0	 0
7	 0	 1	 0
8	 0	 0	 1
;
run;

So Ranking 1 has 2 items at first and second positions, 1 item at fourth and sixth positions. Ranking 2 has 1 at first, 3 at second and etc.

I sure managed to do that separately, one ranking at a time, using SQL "select rank, count(rank)". But I would like to do that just once and couldn't find a solution on my own.

And also, using SQL, it only displays the positions that occurs in the table. Meaning that the first one shows only 1st, 2nd, 4th and 6th positions, but I want every position, with the zeros.

Any thoughts?

Re: Count values one column at a time

FreelanceReinh — Thu, 13 Jan 2022 10:55:07 GMT

Hi @Andre_AA1 and welcome to the SAS Support Communities!

You can create the WANT dataset with a DATA step:

data want(keep=Position n_:);
set have end=last;
length Position 8;
array   rank_[3];
array n_rank_[3];
array p[8,3] _temporary_ (8*(3*0)); /* Dimensions could also be derived from the data. */
do _j=1 to dim2(p);
  p[input(rank_[_j],16.),_j]+1; /* Numeric rank variables would be more appropriate. */
end;
if last;
do Position=1 to dim1(p);
  do _j=1 to dim2(p);
    n_rank_[_j]=p[position,_j];
  end;
  output;
end;
run;

I used numeric variables Position and n_rank_1-n_rank_3. Not sure why you suggested character variables (also for the ranks in dataset HAVE).

Edit: Are your ranks always integers? If not, we can switch from a temporary array p[*] to a hash object.

Re: Count values one column at a time

Ksharp — Thu, 13 Jan 2022 11:45:57 GMT

data have;
infile datalines;
input OBS$ Rank_1$ Rank_2$ Rank_3$;
datalines;
A  1  1  1
B  1  2  1
C  2  2  3
D  4  5  2
E  6  2  8
F  2  7  4
;
run;

proc transpose data=have out=temp;
by obs;
var rank: ;
run;


proc freq data=temp noprint;
table col1*_NAME_/out=temp2 list;
run;

proc transpose data=temp2 out=temp3(drop=_name_ _label_ rename=(col1=position));
by col1;
id _NAME_;
var count;
run;

proc stdize data=temp3 out=want missing=0 reponly;
var _numeric_;
run;

Re: Count values one column at a time

Kurt_Bremser — Thu, 13 Jan 2022 12:30:53 GMT

Transpose, then sum in SQL:

proc transpose data=have out=long;
by obs;
var rank:;
run;

proc sql;
create table want as
  select
    col1 as position,
    sum(_name_ = 'Rank_1') as n_rank1,
    sum(_name_ = 'Rank_2') as n_rank2,
    sum(_name_ = 'Rank_3') as n_rank3
  from long
  group by position
;
quit;