CODEWITHSUNDEEP
sas
doodledad
doodledad

Reputation: 13

Using proc freq to cross tabulate within same ID that has 2 occurences

I have a data set where ID's have 2 different occurences on the same day. There are about 10 different occurences. I want to cross tabulate the occurences using proc freq or proc tabulate & find how many times each instance occurs on the same day. I want my table to look something like this

Frequency  occ1 occ2 occ3 occ4 occ5 occ6    
occ1        2    0    0     1   4    0
occ2        1    0    0     0   0    0
occ3        3    0    0     0   0    0
occ4        0    5    3     0   3    0
occ5        0    2    4     0   5    0
occ6        1    5    4     2   1    2

My data looks something like this

data have;
input id occurrence ;
datalines;
id1  occ3           
id1  occ2
id2  occ1
id2  occ6
id3  occ2
id3  occ4
etc...

i tried

proc freq data=have;
    tables occurrence*occurence ;
    run;

but not having any luck. I have tried other variations & using by ID but it gives every single ID individually & i have about 200 ID numbers.

Thanks!

Upvotes: 0

Views: 62

Answers (1)

Richard
Richard

Reputation: 27498

Reform data so a tabulation of ordered pairs can be done.

data have;
  call streaminit(2022);
  do id = 1 to 20;
    topic = rand('integer', 10); output;
    topic = rand('integer', 10); output;
  end;
run;

data stage;
  do until (last.id);
    set have;
    by id;
    row = col;
    col = topic;
  end;
run;

ods html file='pairfreq.html';
title "Ordered pair counts";
proc tabulate data=stage;
  class row col;
  table row='1st topic in id pair',col='2nd topic in id pair'*n='';
run;
ods html close;

enter image description here

Upvotes: 1

Related Questions