topic Re: Proc SQL count by group when using GE in New SAS User

Proc SQL count by group when using GE

Will2801 — Thu, 29 Jul 2021 21:24:02 GMT

I have 3 tables:
Orders (Cust_ID, Order_Number, PN, Cost)
Parts (PN, Part_Name)
Customers (Cust_ID, Cust_Name)

Cust_ID in the Orders table is equal to Cust_ID in the Customers table.
PN in the Orders table is equal to PN in the Parts table.

Using Proc SQL I need a table with the first column grouped by "Cust_Name" and the second column the times (count) that each Cust_Name has made an order where the "Cost" is greater than 1000 and the "Part_Name" is equal to "Oring".

The result should come out:
Cust_Name Count Orders Greater than 1000 & Part_Name = Oring
Name1 3
Name2 5
......

3 and 5 mean that Name1 made 3 orders each greater than 1000 and Name2 made 5 orders each greater than 1000 and these 8 orders correspond to the Part_Name "Oring".

I have the following schematic but can't finish it to show me the count:

Proc SQL;
select Customers.Cust_Name
from Orders, Parts, Customers
group by Customers.Cust_Name
having
Orders.PN = Parts.PN
and
Orders.Cust_ID = Customers.Cust_ID
and
Orders.Cost ge 1000
and
Parts.Part_Name = 'Oring';

I am missing the syntax for it to display the second column (count), I used Count (*), Count (Orders.Cost), Count (Customers.Cust_Name), but none worked for me.

Thanks for your help.

@Count

Re: Proc SQL count by group when using GE

jimbarbour — Thu, 29 Jul 2021 21:39:01 GMT

Well, first of all, I would switch a lot of the Having clause (which occurs after initial row selection) to the Where clause which screens out rows as part of the initial selection.

count(table_name.column_name) should be correct.

Jim

Re: Proc SQL count by group when using GE

jimbarbour — Thu, 29 Jul 2021 21:50:36 GMT

I might re-vamp the code like the below. Notice that there's a WHERE clause now. Again, the WHERE clause screens out any rows that don't meet your criteria before the initial results set is obtained. Then, the count will only count those rows that match the criteria, and the GROUP BY clause will act as a BY statement would in a Data step, i.e. to cause the count to occur at the Customer level. Notice that I've shortened the table names by using a three letter alias for ease of coding (and reading I think).

Note that "Oring" is case sensitive. If you don't want it to be case sensitive you could use either Lowcase(Part_Name) = 'oring' or Upcase(Part_Name) = 'ORING'. Note also that O-Ring does not equal ORing. If your data is really clean, this shouldn't be a problem, but it's something to be aware of.

Jim

Proc SQL;
	select	Customers.Cust_Name
		,COUNT(*)			AS	Order_Count
		from	Orders		Ord
				,Parts		Prt
				,Customers	Cst
	WHERE	Ord.PN 			=	Prt.PN
		and	Ord.Cst_ID 		=	Cst.Cust_ID
		and	Ord.Cost		ge	1000
		and	Prt.Part_Name	=	'Oring'
		group by Cst.Cust_Name
		;
QUIT;

Re: Proc SQL count by group when using GE

Will2801 — Thu, 29 Jul 2021 23:00:48 GMT

thanks a lot @jimbarbour, I was confusing the HAVING and WHERE clauses, but thanks to your answer the figure is now clearer. I'm just starting out in SAS and his comment helped me a lot.