Proc SQL Subquery

[Batman]

The following code first determines how many Brand Name drugs have just one HCPCS Code and then uses that list to pull Code, Brand Name and Generic name for those drugs from the same data source.   However, is there a way to combine this into one query, perhaps with the first query as a subquery in the second?

proc sql;
Create Table Step2a as select Brand_name, count(*) as Cnt
from Spend_util
Group by Brand_Name
having Count(distinct HCPCS) = 1;

Create Table Step2b as select a.HCPCS label='HCPCS Code', a.Brand_name, a.Generic_Name
from Spend_util as a, Step2a as b
where a.Brand_name=b.Brand_name;
quit;

[Krueger]

Is the only thing wrong with your Step2a dataset that it has the Cnt variable and your label's aren't set yet?

If so just add your labels in that step, your additional columns and add their grouping after the brand name. 

Also get rid of your count(*) as Cnt from the select; it doesn't need to be included as the logic is being applied with your having clause.

Edit: Like this, just compare results make sure they match. If not you might have to play with your group by.

proc sql;
create Table want as 
select HCPCS label='HCPCS Code', Brand_name, Generic_Name
from Spend_util
group by Brand_Name, HCPCS, Generic_Name
having Count(distinct HCPCS) = 1;
quit;

[Batman]

Thanks, but I don't think that would work, since we want to know which Brand Names have more than one HCPCS Code.   If we group by Brand Name, Generic Name and HCPCS, wouldn't the count always be 1?

[Krueger]

I suppose I don't understand how your step 2 is applying that logic as I don't see it. 

If you want counts > 1 change it from = to >.

[Reeza]

Are you assuming it won't work, or did you test it and did it not work?