Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 87
Accepted Solution

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

[ Edited ]

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:

 

MCYearCount_LathCount_Meas
US000409201511
US000409201613
US01036201501
US01036201622
US02044201510
US02044201613
US318201631

 

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.


Accepted Solutions
Solution
‎11-18-2016 05:15 AM
Frequent Contributor
Posts: 87

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


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;

View solution in original post


All Replies
Super User
Posts: 7,465

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

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).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎11-18-2016 05:15 AM
Frequent Contributor
Posts: 87

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


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;
SAS Super FREQ
Posts: 706

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

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 1650 views
  • 2 likes
  • 3 in conversation