topic How to get zero counts of required combinations ? in SAS Programming

How to get zero counts of required combinations ?

sri1 — Thu, 20 Aug 2020 19:19:22 GMT

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

Re: How to get zero counts of required combinations ?

ballardw — Thu, 20 Aug 2020 19:50:05 GMT

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.

Re: How to get zero counts of required combinations ?

ChrisNZ — Thu, 20 Aug 2020 22:39:24 GMT

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

Re: How to get zero counts of required combinations ?

sri1 — Sat, 22 Aug 2020 18:05:49 GMT

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

Re: How to get zero counts of required combinations ?

sri1 — Sat, 22 Aug 2020 18:11:09 GMT

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”

Re: How to get zero counts of required combinations ?

Tom — Sun, 23 Aug 2020 01:00:42 GMT

@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?

Re: How to get zero counts of required combinations ?

sri1 — Sun, 23 Aug 2020 06:43:04 GMT

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

Re: How to get zero counts of required combinations ?

Tom — Sun, 23 Aug 2020 14:01:29 GMT

@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;