SAS Count of a variable for each year and each of other variable

0
votes

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.

2
sqlsas

2 Answers

1
votes

I am a bit lost in your explanations, however, if you want to do two counts like this, you need to make them in separate columns. In SQL case works as a switch and its output is always only one column.

However I do not use SAS, i use Oracle, so proceed with caution. In Oracle the string is captured in apostrophes not in quotes, maybe that is the case for you also.

I would advise to try this instead of your case:

sum(case when t1.ET='Lath' then 1 else 0 end) as count_lath,
sum(case when t1.ET='Meas' then 1 else 0 end) as count_meas

The grouping you have should be OK.

Try this, hope it helps.

0
votes

Yes I got it... Thanks @Emil...

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;