BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
imanojkumar1
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
imanojkumar1
Quartz | Level 8

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

3 REPLIES 3
Kurt_Bremser
Super User

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
Quartz | Level 8

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
SAS Super FREQ

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

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 49920 views
  • 5 likes
  • 3 in conversation