topic Re: sql count using case when group by inside count() in SAS Programming

sql count using case when group by inside count()

leehsin — Fri, 14 Feb 2020 17:38:24 GMT

Hi, I wanted to count events on dates using count() in proc sql. Here is the sample code:

data have0;
 infile cards truncover expandtabs;
 input ID $ CODE DATE :date9. DEPT $ ;
 format date date8. ;
 cards;
A0001 7001 12Oct2015 ETA 
A0001 7002 12Oct2015 ETA 
A0001 7001 12Nov2015 ETA 
A0001 7001 15Nov2016 ETA 
A0001 7002 13Oct2016 ETB 
A0001 7003 13Oct2015 ETB 

A0002 7001 12Oct2015 ETA  
A0002 7002 12Oct2015 ETA  
A0002 7003 12Oct2015 ETA  
A0002 7001 20Oct2016 ETB  
A0002 7002 23Oct2016 ETB  
A0002 7003 30Nov2016 ETB  

;
run;

PROC SQL;
   CREATE TABLE events_ct AS
   SELECT 
		count(*) as line_cnt
        ,count(distinct ID) as ID_cnt
		,count(distinct CODE) as CODE_cnt
		,count(distinct case when DEPT="ETA" then CODE end) as EtA_CODE_cnt
		,count(case when DEPT="ETA" then DATE end) as events_cnt
      FROM have0
;
QUIT;

The results for events_cnt shoud be 3 because I want to count the total distinct dates for each ID.

What I need actually is something like this:

count(distinct case when DEPT="ETA" then (DATE from have0 group by ID) end) as events_cnt

but this code doesn't work as it is now. It there any solution to modify the count(case when DEPT="ETA" then DATE end) as events_cnt and get what I need? Thanks a lot !

Re: sql count using case when group by inside count()

leehsin — Fri, 14 Feb 2020 17:43:32 GMT

Correction: I meant "The results for events_cnt should be 4 because I want to count the total distinct dates for each ID."

Re: sql count using case when group by inside count()

Reeza — Fri, 14 Feb 2020 17:49:16 GMT

Add a GROUP BY clause to tell SAS to process it by specific groups.

GROUP BY ID is what you need.

Re: sql count using case when group by inside count()

leehsin — Fri, 14 Feb 2020 17:52:53 GMT

Where do I put GROUP BY ID? I don't want to put it after FROM Have0 as this will affect other COUNT statements.

Re: sql count using case when group by inside count()

Reeza — Fri, 14 Feb 2020 18:37:09 GMT

In that case that won't work and you need either a subquery or do multiple queries and combine the results. I would probably recommending the second option - do your queries separately and merge them for clarity. It's easier to manage your analysis in this fashion.

Re: sql count using case when group by inside count()

PGStats — Fri, 14 Feb 2020 19:22:17 GMT

Please provide an example showing all the different counts that you want to perform, including expected results.

Re: sql count using case when group by inside count()

leehsin — Fri, 14 Feb 2020 23:00:27 GMT

These are the 4 records I want to count:

A0001 12Oct2015 ETA
A0001 12Nov2015 ETA
A0001 15Nov2016 ETA
A0002 12Oct2015 ETA

Re: sql count using case when group by inside count()

Tom — Sat, 15 Feb 2020 01:32:34 GMT

@leehsin wrote:

These are the 4 records I want to count:

A0001 12Oct2015 ETA
A0001 12Nov2015 ETA
A0001 15Nov2016 ETA
A0002 12Oct2015 ETA

That does NOT look like counts.

What are the counts you want for the 12 observations in your example dataset.

Re: sql count using case when group by inside count()

Tom — Sat, 15 Feb 2020 05:27:38 GMT

Are you looking for something like this?

proc sql;
create table want as
select id
     , count(*) as line_cnt
     , count(distinct code) as code_cnt
     , count(distinct date) as date_cnt
     , count(distinct dept) as dept_cnt
     , count(case when dept='ETA' then 1 end) as eta_cnt
     , count(distinct case when dept='ETA' then code end) as eta_code_cnt
     , count(distinct case when dept='ETA' then date end) as eta_date_cnt
     , count(distinct case when dept='ETA' then dept end) as eta_dept_cnt
from have
group by id
;
quit;

                                                               eta_      eta_      eta_
Obs   ID    line_cnt  code_cnt  date_cnt  dept_cnt  eta_cnt  code_cnt  date_cnt  dept_cnt

 1   A0001      6         3         5         2        4         2         3        1
 2   A0002      6         3         4         2        3         3         1        1

Re: sql count using case when group by inside count()

ChrisNZ — Sat, 15 Feb 2020 02:21:37 GMT

This counts what you want:

  count(unique case when DEPT="ETA" then cats(DATE,CODE) end) -1 as EVENTS_CNT

You need to substract 1 as an empty string will also be counted when DEPT <> 'ETA'

Re: sql count using case when group by inside count()

leehsin — Mon, 17 Feb 2020 14:36:10 GMT

I meant these are the 4 records to be counted, ---- 3 different dates for A0001, and 1 different date for A0002.

Re: sql count using case when group by inside count()

leehsin — Mon, 17 Feb 2020 15:50:36 GMT

Thank you, ChrisNZ! This is the solution I am looking for.

I just change 'CODE' to 'ID' as I want to group the count of dates by ID.

count(unique case when DEPT="ETA" then cats(DATE,ID) end) as EVENTS_CNT

The result to have:

lin_cnt ID_cnt CODE_cnt EtA_CODE_cnt envents_cnt

13 2 3 3 4