proc sql using where clause

[HN2001]

I'm trying to use where in the select statement of sql but it isn't working properly.

I have data like the following

Final_Decision	Item Type
Keep	Type1
Keep	Type1
Keep	Type1
Keep	Type2
Exclude	Type2
Keep	Type2
Keep	Type1
Exclude	Type1
Keep	Type2
Exclude	Type2
Keep	Type2
Keep	Type1
Keep	Type1
Keep	Type1
Keep	Type1
Keep	Type1

I'm looking for a table like this

Item Type	Total Items	Number not excluded
Type1	10	9
Type2	6	4

I have the following sas code but the second select statement is calculating the whole number not excluded for each item type

any suggestions

proc sql;
create table typ_out as
select Item_Type, Count(Item_Type) as N_total, (select Count(Item_Type) from both where Final_Decision ne "Exclude") as N_remain
from Both
group by Item_Type;
quit;

[novinosrin]

data have;
infile cards truncover;
input Final_Decision $	ItemType $;
cards;
Keep	Type1
Keep	Type1
Keep	Type1
Keep	Type2
Exclude	Type2
Keep	Type2
Keep	Type1
Exclude	Type1
Keep	Type2
Exclude	Type2
Keep	Type2
Keep	Type1
Keep	Type1
Keep	Type1
Keep	Type1
Keep	Type1
;



proc sql;
create table want as
select ItemType,count(ItemType) as c,sum(Final_Decision='Keep') as c1
from have
group by ItemType;
quit;

[HN2001]

Your suggestion works well but is there any reason why the count statement wouldn't work?

Additionally, if I had another column of data as times

data have;
infile cards truncover;
input Final_Decision $ ItemType $ Time;
cards;
Keep Type1 2
Keep Type1 3
Keep Type1 2
Keep Type2 4
Exclude Type2 9
Keep Type2 3
Keep Type1 2
Exclude Type1 11
Keep Type2 1
Exclude Type2 10
Keep Type2 2
Keep Type1 3
Keep Type1 3
Keep Type1 4
Keep Type1 5
Keep Type1 3
;

I also tried the where statement again since I can't do avg(time where Final_Decision ne 'Exclude'). Can you offer help on that as well?

[novinosrin · Accepted solution]

See if this is what you want for the new sample with time?

data have;
infile cards truncover;
input Final_Decision $ ItemType $ Time;
cards;
Keep Type1 2
Keep Type1 3
Keep Type1 2
Keep Type2 4
Exclude Type2 9
Keep Type2 3
Keep Type1 2
Exclude Type1 11
Keep Type2 1
Exclude Type2 10
Keep Type2 2
Keep Type1 3
Keep Type1 3
Keep Type1 4
Keep Type1 5
Keep Type1 3
;


proc sql;
create table want as
select ItemType,count(ItemType) as c,sum(Final_Decision='Keep') as c1, mean(ifn(Final_Decision='Keep',1,.)*time) as avg
from have
group by ItemType;
quit;

[HN2001]

yes that is what I am looking for.  Thanks for giving me an alternative to getting those calculations.

[novinosrin]

Most Welcome. Have a good one!

[Reeza]

Any reason you're not just using PROC FREQ here?

[HN2001]

I'm doing other calculations besides frequencies so I was experimenting with sql to do my freqs and averages in one data step.

[Reeza]

---

@HN2001 wrote:

I'm doing other calculations besides frequencies so I was experimenting with sql to do my freqs and averages in one data step.

---

You can do some of that with weights statement, not sure about the average portion though. 

You can do it in SQL but it is essentially hard coding in your rules and values so if something changes your code needs to change. 

[PGStats]

If you want to use a subquery, it must be correlated with the main query:

data have;
infile cards truncover;
input Final_Decision $	ItemType $;
cards;
Keep	Type1
Keep	Type1
Keep	Type1
Keep	Type2
Exclude	Type2
Keep	Type2
Keep	Type1
Exclude	Type1
Keep	Type2
Exclude	Type2
Keep	Type2
Keep	Type1
Keep	Type1
Keep	Type1
Keep	Type1
Keep	Type1
;

proc sql;
create table typ_out as
select 
    ItemType, 
    Count(*) as N_total, 
    (select Count(*) from have 
        where Final_Decision ne "Exclude" and itemtype = a.itemtype) as N_remain
from have as a
group by ItemType;
quit;

[Ksharp]

data have;
infile cards truncover;
input Final_Decision $ ItemType $ Time;
cards;
Keep Type1 2
Keep Type1 3
Keep Type1 2
Keep Type2 4
Exclude Type2 9
Keep Type2 3
Keep Type1 2
Exclude Type1 11
Keep Type2 1
Exclude Type2 10
Keep Type2 2
Keep Type1 3
Keep Type1 3
Keep Type1 4
Keep Type1 5
Keep Type1 3
;


proc sql;
create table want as
select ItemType,count(ItemType) as totalitems,
sum(Final_Decision ne 'Exclude') as number_not_excluded
from have
group by ItemType;
quit;