How to get zero counts of required combinations ?

[sri1]

Hi 

I have a  sample dataset as below  with many records and tried the following code to get zero counts with required combinations  , but am getting all the possible combinations along with  the relevant group, even though  those records doesn't belong to that group.Is there anyway I can get the desired output as stated below

proc format;
value $sc(notsorted)
"low"="low"
"medium"="medium"
"high"="high";
run;

data test;
infile cards;
input cat $ code $ score $;
cards;
c1 a11 low
c1 b11 medium
c2 e12 low
c2 h13 medium
;
run;

data test2;
set test;
format score $sc.;
run;

proc summary data=test2 nway completetypes;
class cat code score/preloadfmt;
output out=test_cnts;
run;

Result:

I need the output only with rows of respective by group, as below

Desired output:

c1  a11 high          0

c1  a11  low           1

c1  a11  medium    0

c1  b11 high           0

c1  b11  low           0

c1  b11  medium    1

c2  e12 high           0

c2  e12  low           1

c2  e12  medium    0

c2 h13 high            0

c2 h13 low             0

c2 h13 medium     1

Highly appreciate your input

Thanks

[ballardw]

For this data one way but can get pretty cumbersome for larger specific combinations.

 

proc summary data=test2 nway completetypes;
class cat code score/preloadfmt;
output out=test_cnts (where= ( (cat='c1' and code in ('a11' 'b11')) OR
                               (cat='c2' and code in ('e12' 'h13')) 
                             )
                     )
;
run;

Another is to create a data set with the combinations of values you want and use the Exclusive=thatdatasetname.

[sri1]

Thanks for reply. This may work for small dataset with minimal combinations but a dataset with hundreds of records it’s bit complex ... Would appreciate if you can post the other way you said with exclusive= datasetname

[Tom]

---

@sri1 wrote:
Thanks for reply. This may work for small dataset with minimal combinations but a dataset with hundreds of records it’s bit complex ... Would appreciate if you can post the other way you said with exclusive= datasetname

---

I am not sure what the requirement is?  Do you want all possible combinations or just some of them?  If the later can you explain the rules for which combinations you want?

[sri1]

I just need the combinations of respective by group such as below

C1 a11 low
C1 a11 medium
C1 a11 high
C1 b11 low
C1 b11 medium
C1 b11 high
C2 e12 low
C2 e12 medium
C2 e12 high
C2 h13 low
C2 h13 medium
c2 h13 high

[Tom · Accepted solution]

---

@sri1 wrote:
I just need the combinations of respective by group such as below

C1 a11 low
C1 a11 medium
C1 a11 high
C1 b11 low
C1 b11 medium
C1 b11 high
C2 e12 low
C2 e12 medium
C2 e12 high
C2 h13 low
C2 h13 medium
c2 h13 high

---

So I think you are saying you want the ACTUAL values of the first two and the possible values of the third.  So use the posted code by PROC SUMMARY but use BY for the first two variables and CLASS for the third with COMPLETETYPES and PRELOADFMT options.

proc summary data=test nway completetypes;
 by cat code ;
 class score/preloadfmt;
 output out=test_cnts;
run;

[ChrisNZ]

Another way:

data test;
infile cards;
input cat $ code $ score $;
cards;
c1 a11 low
c1 b11 medium
c2 e12 low
c2 h13 high
run;

proc sql;
  select a.CAT, a.CODE, b.SCORE, c.SCORE=b.SCORE
  from (select CAT, CODE from TEST)     a
         full join
       (select unique SCORE from TEST)  b 
         on 1
         left join 
       TEST                              c
         on a.CAT=c.CAT and a.CODE=c.CODE
  order by 1,2; 

cat	code	score
c1	a11	low	1
c1	a11	medium	0
c1	a11	high	0
c1	b11	medium	1
c1	b11	low	0
c1	b11	high	0
c2	e12	high	0
c2	e12	medium	0
c2	e12	low	1
c2	h13	medium	0
c2	h13	low	0
c2	h13	high	1

[sri1]

Thanks for reply. I am not sure whether this code works for the dataset posted as it doesn’t have a record with score “high”