topic Re: proc sql using where clause in SAS Procedures

proc sql using where clause

HN2001 — Tue, 25 Sep 2018 21:30:22 GMT

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;

Re: proc sql using where clause

novinosrin — Tue, 25 Sep 2018 21:41:06 GMT

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;

Re: proc sql using where clause

HN2001 — Tue, 25 Sep 2018 21:57:29 GMT

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?

Re: proc sql using where clause

novinosrin — Tue, 25 Sep 2018 22:03:14 GMT

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;

Re: proc sql using where clause

Reeza — Tue, 25 Sep 2018 22:11:21 GMT

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

Re: proc sql using where clause

HN2001 — Tue, 25 Sep 2018 22:15:44 GMT

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

Re: proc sql using where clause

HN2001 — Tue, 25 Sep 2018 22:23:33 GMT

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

Re: proc sql using where clause

novinosrin — Tue, 25 Sep 2018 22:24:47 GMT

Most Welcome. Have a good one!

Re: proc sql using where clause

Reeza — Tue, 25 Sep 2018 22:31:38 GMT

@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.

Re: proc sql using where clause

PGStats — Wed, 26 Sep 2018 03:42:25 GMT

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;

Re: proc sql using where clause

Ksharp — Wed, 26 Sep 2018 13:29:29 GMT

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;