SQL to PROC SQL- partition By alternative (min case)

Question

I am new to SAS but know sql so trying to use SQL code to write proc sql code and realized that PARTITION by is not available in SAS.

Table

Customer_id Item_type   Order Size  Date        ….

 1. A401    Fruit           Small      3/14/2016    …. 
 2. A401    Fruit           Big        5/22/2016    …. 
 3. A401    Vegetable       Small      7/12/2016    ….
 4. B509    Vegetable       Small      3/25/2015    …. 
 5. B509    Vegetable       Big        3/15/2014    …. 
 6. B509    Vegetable       Small      3/1/2014     ….

Explanation

  Customer_id   Item_Type   Count       Reason
1.A401          Fruit       2           X-WRONG-because date corresponding  big item is later than others in group  
2.B509          Vegetable   2           RIGHT-Note that count is 2 only because one of the dates is earlier than the Big corresponding item(3/1/2014 is earlier than 3/15/2014) 

SQL Output

  Customer_id   Item_Type   Count       
1.B509          Vegetable       2   

select t.customer_id, t.item_type, count(*)
from (select t.*,
         min(case when OrderSize = 'Big' then date end) over (partition by customer_id, item_type) as min_big
  from t
 ) t
where date > min_big
group by t.customer_id, t.item_type;

Answer 1

In SQL dialects (MS Access, MySQL, SQLite, SAS' proc sql) that do not support window functions, most PARTITION BY calls can be replaced with correlated aggregate subqueries which is supported by all major SQL dialects. Consider the following adjustment:

select main.customer_id, main.item_type, count(*) as count
from 
   (select t.customer_id, t.item_type, t.date,
          (select min(case when OrderSize = 'Big' then date end) 
           from t sub
           where sub.customer_id = t.customer_id
           and sub.item_type = t.item_type) as min_big
    from t
   ) main
where main.date > main.min_big
group by main.customer_id, main.item_type;