BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
HN2001
Obsidian | Level 7

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_DecisionItem Type
KeepType1
KeepType1
KeepType1
KeepType2
ExcludeType2
KeepType2
KeepType1
ExcludeType1
KeepType2
ExcludeType2
KeepType2
KeepType1
KeepType1
KeepType1
KeepType1
KeepType1

 

I'm looking for a table like this

Item TypeTotal ItemsNumber not excluded
Type1109
Type264

 

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;

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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;

 

 

View solution in original post

10 REPLIES 10
novinosrin
Tourmaline | Level 20
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
Obsidian | Level 7
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
Tourmaline | Level 20

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
Obsidian | Level 7

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

novinosrin
Tourmaline | Level 20

Most Welcome. Have a good one!

Reeza
Super User
Any reason you're not just using PROC FREQ here?
HN2001
Obsidian | Level 7

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

Reeza
Super User

@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
Opal | Level 21

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;
PG
Ksharp
Super User
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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1397 views
  • 2 likes
  • 5 in conversation