proc sql sql count using case when group by inside count()

[A_Swoosh]

Hi, I wanted to count events on dates using count() in proc sql.

I want to:

1. Count the first date as OPEN if after certain date

2. Count the last date as CLOSED if before certain date

3. Count the number of visits between OPEN and close

All using proc sql.

Here is the sample code:

data have0;
 infile cards truncover expandtabs;
 input STORE_ID $ CUSTOMER_ID DATE :date9. ;
 format date date8. ;
 cards;
A0001 7001 12Oct2015 
A0001 7002 12Oct2015 
A0001 7001 12Nov2015 
A0001 7001 15Nov2016 
A0001 7002 13Oct2016 
A0001 7003 13Oct2015 

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

;
run;
proc sql;
    create table test as
    select distinct store_id,
    max(date) as close_date,
    min(date) as open_date,
    count(distinct case when min(date) ge '12-OCT-2016'd and max(date) le '30-NOV-2019'd) then ) as  /*not sure syntax here*/
    count(distinct customer_id) as no_customers  /*not sure if this should be based on previous line?*/
    group by store_id;
quit;

I'm not sure how to format the count distinct case to capture all transactions between the open and close dates.

Similarly, I want to count the number of distinct customers between those dates too.

Want:

Any help would be appreciated!

[maguiremq]

Could you also post a `want` data set containing what you want the data to ultimately look like? Just take your `have` DATA step and reduce it down to what you want. I've also formatted your code to make it more readable for others.

proc sql;    
	create table 	test as
		select 
					distinct store_id,
					max(date) as close_date,
					min(date) as open_date, 
					count(distinct case when min(date) ge '12-OCT-2016'd and max(date) le '30-NOV-2019'd) then ) as  /*not sure syntax here*/ 
					count(distinct customer_id) as no_customers  /*not sure if this should be based on previous line?*/ 
		group by 
					store_id;
quit;

[A_Swoosh]

Added a want dataset. And I thank you for formatting. On my screen it looks fine--strange.

[Reeza]

This is a case where a data step is much more efficient, is there a particular reason for SQL?

[A_Swoosh]

No particular reason actually--just what I thought would be ideal in this particular case.

[Reeza · Accepted solution]

This may be what you're looking for then. Your initial explanation was confusing to me. 

proc sql;
create table want as 
select store_ID, count(distinct customer_ID) as no_customers,
count(*) as n0_transactions
from have0
where date between '12OCT2016'd and '30Nov2019'd;
quit;

---

@A_Swoosh wrote:

Hi, I wanted to count events on dates using count() in proc sql.

I want to:

1. Count the first date as OPEN if after certain date

2. Count the last date as CLOSED if before certain date

3. Count the number of visits between OPEN and close

All using proc sql.

Here is the sample code:

data have0;
 infile cards truncover expandtabs;
 input STORE_ID $ CUSTOMER_ID DATE :date9. ;
 format date date8. ;
 cards;
A0001 7001 12Oct2015 
A0001 7002 12Oct2015 
A0001 7001 12Nov2015 
A0001 7001 15Nov2016 
A0001 7002 13Oct2016 
A0001 7003 13Oct2015 

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

;
run;
proc sql;
    create table test as
    select distinct store_id,
    max(date) as close_date,
    min(date) as open_date,
    count(distinct case when min(date) ge '12-OCT-2016'd and max(date) le '30-NOV-2019'd) then ) as  /*not sure syntax here*/
    count(distinct customer_id) as no_customers  /*not sure if this should be based on previous line?*/
    group by store_id;
quit;

I'm not sure how to format the count distinct case to capture all transactions between the open and close dates.

Similarly, I want to count the number of distinct customers between those dates too.

Want:

Any help would be appreciated!

---

[A_Swoosh]

Thank you for the quick reply. This was just a simplified case I had. Open and close dates are going to vary based on store so using predefined dates would not always work which is why I was trying to create a case scenario based on the min and max dates.

[Reeza]

Well....I can only answer the question as posted ...

I would instead then create a second table with each store ID and the start/end date. Then you can modify the query posted by joining the have0 table to the lookup table with the dates on Store ID and change the dates in the BETWEEN operator to be the variables from the look up table.