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
@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;
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 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 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;
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 |
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.