ZeekDSA ZeekDSA - 19 days ago 6
SQL Question

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

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.

Answer

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.