How to complete an incomplete binary matrix?

[genemroz]

Esteemed Advisers,

I’ve been away from SAS coding for a while so I’m probably overlooking a relatively simple solution.  Here’s the problem (greatly simplified to aid understanding):

A Camera Network has three Cameras.  Camera C1  can take one of two different Orientations (O1,O2) to cover, respectively, two targets (T1,T2). The other two cameras (C2,C3) can also take on two orientations (O1, O2).  Because they have different optical properties, C2 can cover only T1 with  O1 and C3 can cover only T2  with orientation O2. 

So the coverage matrix (k) for C1 looks like:

        O1     O2

T1      1        1

T2     1        1

For C2:

        O1     O2

T1      1        0

T2     0        0

For C3:

        O1     O2

T1      0        0

T2     0        1

My HAVE dataset (see code below) produces the correct coverage matrix for C1. However, for C2 and C3  the dataset only contains observations where an orientation covers a target and none for orientations that do not cover targets.  My WANT dataset must contain all possibilities of coverage (k=1) or non-coverage (k=0) for all cameras and all target/orientation combinations.

See the attached code for exemplar Have/Want code and a failed attempt to use Update. 

Looking at these simple coverage matrices suggests to me that perhaps some kind of array processing might be called for but I’m not proficient in that area of SAS.  

My real-life application consists of  several cameras, hundreds/thousands of targets and 24 possible orientations.  The resulting binary matrix is needed for input to Proc Optmodel for optimizing camera orientation.  I’d like to find a dynamic solution that doesn’t require operator intervention.

Thanks in advance for any help you can provide,

Gene

data Have;
input C $ O $ T $ k;
cards;
C1 O1 T1 1
C1 O1 T2 1
C1 O2 T1 1
C1 O2 T2 1
C2 O1 T1 1
C3 O2 T2 1
run;
proc sort data=Have;
Title "Have";
by T C O;
run;

Proc freq data=Have;
Title "Have";
tables C*O;
run;

data Want;
input C $ O $ T $ k;
cards;
C1 O1 T1 1
C1 O1 T2 1
C1 O2 T1 1
C1 O2 T2 1
C2 O1 T1 1
C2 O1 T2 0
C2 O2 T1 0
C2 O2 T2 0
C3 O1 T1 0
C3 O1 T2 0
C3 O2 T1 0
C3 O2 T2 1
;

proc sort data=want;
by T C O;
run;

Proc freq data=want;
Title "Want";
tables C*O;
run;

Proc sort data=want;
by C;
run;

Proc freq data=want;
by C;
Title "Want";
tables T*O;
run;

/* Failed attempt to create Want (for only C2) using Update */
data Master;
set Have;
where C ='C2';
run;

Proc sort data=Master;
by T O;
run;

Data Transaction;
set Have (rename=(k=k0));
by T C;
where C='C1';
run;

Proc sort data=Transaction;
by  T O;
run;

Data WantbyUPDATE;
update Master Transaction;
by T O;
if missing(k) then kk=0; else kk=k;
run;

proc sort data=WantbyUPDATE;
by C O;

Proc freq data=WantbyUPDATE;
Title "WantbyUPDATE";
tables C*O;
run;

[FreelanceReinh · Accepted solution]

Hello @genemroz,

Try PROC FREQ with the SPARSE option

proc freq data=have noprint;
tables T*C*O / sparse out=want(rename=(count=k) drop=percent);
run;

or PROC SUMMARY with the NWAY and COMPLETETYPES options

proc summary data=have nway completetypes;
class T C O;
output out=want(rename=(_freq_=k) drop=_type_);
run;

[genemroz]

Brilliant! Thank you so much!
Gene