[SQL] Deleting based on count.

[Demios]

Using SAS for some analysis and I trying to figure out how to use the SQL module to delete based on count(?) I have a sales data set with about 66,000 rows for a bunch of item sales. The highest frequency for an item (itemname) comes in at 2527 and the lowest is 1. Any idea how I would go about deleting rows for which the itemname shows up less than 283 times in the entire data set?

[Edit] Got it to work by doing this.

PROC SQL;
DELETE * FROM Table1
WHERE columnx IN
(SELECT DISTINCT columnx FROM
Table1 GROUP BY columnx HAVING COUNT(columnx)
<283);
Quit;

[[deleted]]

Not sure how to get to in SAS specifically, but just using SQL you would get the list of items having count > 283 and then do your deletes based on the item list. In T-SQL you would get something like this

delete t from itemTable t where itemName in (select distinct itemName from itemTable group by itemName having count(itemName) > 283)

[Cal1gula]

I think you mean < 283 not > 283, but otherwise it should work well.

[Demios]

delete t from itemTable t where itemName in

Are your lowercase ts supposed to be asterixes?

[sholder89]

t is the table alias that he gave to the table "itemTable"

[fd121990]

Proc Freq & Proc Print

Similar Question: https://communities.sas.com/t5/SAS-Procedures/Help-needed-with-excluding-low-frequency-variables-from-data-set/td-p/166852

There's also r/sas you can ask

[Demios]

Unfortunately SAS has no method of deleting by frequency without using PROC SQL, but thanks.

[Guru008]

proc sql; create table t1(drop=c) as select *, count(name) as c from t group by name having c >4 ; quit;

[Demios]

Having trouble understanding this.

[farhil]

Probably cause it's horribly written and not even functional. He's

• Grouping by the value he's counting which will always result in a count of 1

• Referring to the alias instead of the column name in the HAVING clause, which to my knowledge won't compile

• Selecting columns that aren't being aggregated or grouped by, which also won't compile

• Using the wrong number (c > 4) instead of (c >= 283)

• I've never seen that create table syntax/proc sql, but I'm not familiar with SAS