How to correctly use CASE when in Proc SQL to count some specific values of a VAR

[imanojkumar1]

I am not getting the results like what I expected.

This is my dataset:

data have0;
 infile cards truncover expandtabs;
 input MC $ ET $ Date :date9. Time :time. PMC $ PXMC $ Site $ Dia MV SF;
 format date date8. Time HHMM.;
 cards;
US000409 Meas 12Oct2015 17:26 7101 Et1 1 . 2780462.00000 1
US000409 Meas 12Nov2016 17:33 7101 Et1 1 861.26 2780462.00000 1
US000409 Lath 12Oct2015 17:33 7102 Et1 1 861.6 2780462.00000 1
US01036 Meas 12Nov2016 17:26 7101 Et1 2 . 522860.00000 1
US01036 Lath 13Oct2016 17:33 7202 Et1 2 866.68 522860.00000 1
US01036 Meas 13Oct2015 17:33 7101 Et1 2 867.36 522860.00000 1
US02044 Meas 13Nov2016 17:26 7202 Et1 1 . 569298.00000 1
US02044 Lath 13Nov2015 17:33 7202 Et1 1 865.32 569298.00000 1
US02044 Meas 14Nov2016 17:33 7202 Et1 2 865.68 569298.00000 1
US318 Lath 14Nov2016 17:26 7101 Et2 2 . 2630856.00000 1
US318 Meas 14Nov2016 17:33 7202 Et2 3 863.26 2630856.00000 1
US318 Lath 14Nov2016 17:33 7202 Et2 3 863.94 2630856.00000 1
US000409 Meas 15Nov2016 21:56 7202 Et2 3 860.98 2780462.00000 1
US000409 Meas 15Nov2016 21:56 7203 Et2 4 861.5 2780462.00000 1
US01036 Lath 16Nov2016 21:56 7101 Et2 4 866.64 522860.00000 1
US01036 Meas 16Nov2016 21:56 7202 Et2 4 867.34 522860.00000 1
US02044 Lath 17Nov2016 21:56 7203 Et2 1 865.3 569298.00000 1
US02044 Meas 17Nov2016 21:56 7204 Et2 3 865.68 569298.00000 1
US318 Lath 17Nov2016 21:56 7204 Et2 2 863.24 2630856.00000 1
;
run;

What I am trying to do is:

PROC SQL;
   CREATE TABLE MC_ET AS 
   SELECT t1.MC, 
            case t1.ET 
                when "Lath" then (COUNT(t1.ET)) AS COUNT_of_ET_Lath
                when "Meas" then (COUNT(t1.ET)) AS COUNT_of_ET_Meas
            end, 
            (year(t1.Date)) AS Year
      FROM have0 t1
      GROUP BY t1.MC, (CALCULATED Year);
QUIT;

I was expecting to get results something like here:

MC	Year	Count_Lath	Count_Meas
US000409	2015	1	1
US000409	2016	1	3
US01036	2015	0	1
US01036	2016	2	2
US02044	2015	1	0
US02044	2016	1	3
US318	2016	3	1

I am not sure how to use when case in the above code.

I think I am doing some mistakes.

Please help in correction.

Thanks.

[Kurt_Bremser]

Try this:

proc sql;
create table mc_et as 
select
  mc,
  (sum(et='Lath')) as count_of_et_lath,
  (sum(et='Meas')) as count_of_et_meas,
  (year(date)) as year
from have0
group by mc, calculated year
;
quit;

It makes use of the fact that the result of a condition is either 1 (true) or 0 (false).

[imanojkumar1 · Accepted solution]

PROC SQL;
   CREATE TABLE MC_ET AS
   SELECT t1.MC, (year(t1.Date)) AS Year,
                sum(case when t1.ET='Lath' then 1 else 0 end) as Count_of_Lath,
                sum(case when t1.ET='Meas' then 1 else 0 end) as count_of_Meas
      FROM have t1
      GROUP BY t1.MC, (CALCULATED Year)
;
QUIT;

[BrunoMueller]

Hi

With the CASE expression, you can only create on column. To do what you want, the SQL can look like this:

PROC SQL;
  CREATE TABLE MC_ET AS 
    SELECT
      t1.MC
      , (year(t1.Date)) AS Year
      , count(
        case t1.ET 
          when "Lath" then 1
          else .
        end ) as COUNT_of_ET_Lath
    , count( 
      case t1.ET 
        when "Meas" then 1
        else .
      end) as COUNT_of_ET_Meas
  FROM
    have0 t1
  GROUP BY
    t1.MC
    , (CALCULATED Year)
  ;
QUIT;

Bruno