Setting Null values for Case when in proc sql

Question

I have a dataset

Period  Brand   ID
  Jan    A      X1
  Jan    A      K1
  Jan    B      CT2
  Feb    C      X2
  Feb    A      P4

I would like to do a distinct count of ID for each brand under each period. I tried using CASE WHEN in proc sql to count the distinct number in each period, but I am unsure of what to set for the else portion, since I am guessing SAS will also count the else part as a distinct item also. My code is as below:

 proc sql;

    create table items as
    select period,
    count(distinct case when brand="A" then ID else "." end) as Count_A,
    count(distinct case when brand="B" then ID else "." end) as Count_B,
    count(distinct case when brand="C" then ID else "." end) as Count_C
from Data
group by period;
quit;

I do understand that I am able to use sub query to construct each count variable also, but the code is likely to get very long and tedious.

Thank you!

Answer 1

I think your query should work so long as you use NULL for the ELSE condition. You can achieve this by simply not listing any ELSE condition, in which case NULL would be used by default.

select
    period,
    count(distinct case when brand = "A" then ID end) as Count_A,
    count(distinct case when brand = "B" then ID end) as Count_B,
    count(distinct case when brand = "C" then ID end) as Count_C
from Data
group by period

Answer 2

This should work for you:-

/*Creating Dataset*/
data a;
input Period $3. Brand$1. Id$3.@;
datalines;
JanAX1
JanAK1
JanBCT2
FebCX2
FebAP4
JanA
FebB
JanAX1
FebCX2
;
run;

/*Counting distinct by giving another condition for Id with brand should do 
  the job for you*/
 proc sql;

    create table items as
    select period,
    count(distinct case when (brand="A" and ID not=" ") then ID end) as 
    Count_A,
    count(distinct case when (brand="B" and ID not=" ") then ID end) as 
    Count_B,
    count(distinct case when (brand="C" and ID not=" ") then ID end) as 
    Count_C
from a
group by period;
quit;

Hope this helps :-)

Answer 3

If you don't want to hard code each column, then add Brand to the group by statement, then transpose the result. This is a useful method if there lots of different brands.

data have;
input Period $ Brand $ ID $;
datalines;
Jan    A      X1
Jan    A      K1
Jan    B      CT2
Feb    C      X2
Feb    A      P4    
;
run;

proc sql;
create table temp
as select
    period,
    brand,
    count(distinct id) as count
from have
group by period, brand;
quit;

proc transpose data=temp out=items (drop=_NAME_) prefix=Count_;
by period;
id brand;
var count;
run;