Diagonal Frequency within Proc Freq

[david27]

Hello,

I have the below dataset.

Is there an option or somethin within proc freq that would give me the diagonal frequency (i.e. sum of count when x=y)?

Also, is there a method/option that can give me frequencies for counts when they are 1(or more) cell above/below the diagonal?

data have;
input x y;
datalines;
18 1
9 8
18 9
12 8
6 6
19 3
15 18
9 12
20 9
2 3
7 2
1 5
3 18
12 12
12 18
5 8
3 4
12 5
2 14
19 19
12 5
12 15
12 18
17 19
9 10
8 20
5 17
8 16
16 18
3 12
18 5
12 14
16 16
6 10
13 18
13 9
17 5
3 18
10 10
;
run;

[Reeza · Accepted solution]

No, but it's easy to synthesize in PROC SQL.

You can then use PROC TRANSPOSE to flip your data if desired. 

*diagonal;
proc sql;
create table want1 as
select x, y, count(*) as N_occurences
from have
group by x, y;
quit;

*above diagonal;
proc sql;
create table want2 as
select x, y, count(*) as N_occurences
from have
where y>x
group by x, y;
quit;

* diagonal + above diagonal;
proc sql;
create table want3 as
select x, y, count(*) as N_occurences
from have
where y>=x
group by x, y;
quit;

if you then want it in a matrix type structure use PROC TRANSPOSE. 

Transposing data tutorials:
Long to Wide:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

https://stats.idre.ucla.edu/sas/modules/reshaping-data-long-to-wide-using-the-data-step/

---

@david27 wrote:

Hello,

I have the below dataset.

Is there an option or somethin within proc freq that would give me the diagonal frequency (i.e. sum of count when x=y)?

Also, is there a method/option that can give me frequencies for counts when they are 1(or more) cell above/below the diagonal?

data have;
input x y;
datalines;
18 1
9 8
18 9
12 8
6 6
19 3
15 18
9 12
20 9
2 3
7 2
1 5
3 18
12 12
12 18
5 8
3 4
12 5
2 14
19 19
12 5
12 15
12 18
17 19
9 10
8 20
5 17
8 16
16 18
3 12
18 5
12 14
16 16
6 10
13 18
13 9
17 5
3 18
10 10
;
run;

---

[Jagadishkatam]

Please try the below code where I created the ID variable with value 'x=y' and used proc freq with subset of x eq y and in table statement id, hope it helps

data have;
input x y;
id='x=y';
datalines;
18 1
9 8
18 9
12 8
6 6
19 3
15 18
9 12
20 9
2 3
7 2
1 5
3 18
12 12
12 18
5 8
3 4
12 5
2 14
19 19
12 5
12 15
12 18
17 19
9 10
8 20
5 17
8 16
16 18
3 12
18 5
12 14
16 16
6 10
13 18
13 9
17 5
3 18
10 10
;
run;

proc freq data=have;
where x eq y;
table id/out=want;
run;

[Reeza]

I stand corrected 🙂

For the second option, change the where to be:

WHERE Y >=X before doing your proc freq.


Will provide diagonals and upper triangle.
proc freq data=have;
where Y>=X;
table X*Y;
run;