Thank you Sunil! the kind of examples you provided makes it easy to understand the sub queries better, proc sql will definitely reduce the number of steps required to derive variable based on summary statistics.