- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
(The question is on PDF p207, Q3, answer is on p241)
While reading the question, because I did not pay enough attention and ignored the requirement of using sql subquery, I first used two separate sql steps (a create table step and a sql join step) like this:
proc sql;
create table countrycodes as
select distinct countrycode
from sq.globalmetadata
where upcase(region)=upcase("Europe & Central Asia") and
upcase(incomegroup)=upcase("High income");
select * from countrycodes;
quit;
proc sql;
select g.countrycode,
estyear1/100 as estpct1 format=percent8.2,
estyear3/100 as estpct3 format=percent8.2,
estyear1
from sq.globalfindex as g inner join
countrycodes as c
on g.countrycode=c.countrycode
where indicatorname="Borrowed for health or medical purposes (% age 15+)"
order by estyear1 desc;
quit;
And then I thought using select into :list; could make the codes simpler, but the &list in the where in; statement of the next step needs quotation marks for character strings, and I forgot the syntax of quote() function(I did not even remember whether it was a function or an option that do not has brackets), I tried something like quote, quotes, quotation and did not see the keywords turns into blue color (which often indicates the keyword was spelled correctly, but here the correct one is grey), and then I searched documents for about 10 minutes and found the correct syntax is quote, so I wrote something like this:
proc sql;
select distinct quote(countrycode)
into :countrycodelist separated by ","
from sq.globalmetadata
where upcase(region)=upcase("Europe & Central Asia") and
upcase(incomegroup)=upcase("High income");
quit;
%put &countrycodelist;
proc sql;
select countrycode, indicatorname,
estyear1/100 as estpct1 format=percent8.2,
estyear3/100 as estpct3 format=percent8.2,
estyear1
from sq.globalfindex
where indicatorname="Borrowed for health or medical purposes (% age 15+)"
and countrycode in (&countrycodelist)
order by estyear1 desc;
quit;
Until I see the correct answer, which uses a subquery in one sql step to solve the question. What I learnt is sql subquery sometimes can make things a lot more easier by saving certain base and macro steps, especially when one does not remember the detailed syntax of the base and macro step. On the other hand, however, when a sql subquery need to be developed to a macro, one may split the sql subquery into separate base steps, as doing so makes set different parameters possible.
proc sql;
select countrycode, indicatorname,
estyear1/100 as estpct1 format=percent8.2,
estyear3/100 as estpct3 format=percent8.2,
estyear1
from sq.globalfindex
where indicatorname="Borrowed for health or medical purposes (% age 15+)"
and countrycode in (select countrycode
from sq.globalmetadata
where upcase(region)=upcase("Europe & Central Asia") and
upcase(incomegroup)=upcase("High income")
)
order by estyear1 desc;
quit;
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
You made a good point about breaking up code into smaller chunks for ease of setting parameters and sometimes easier for maintenance. I would like to add that there is a rule for using non-correlated subquery such as this example, in which you can only return values from a SINGLE column from the inner query. That may be limiting for some.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
You made a good point about breaking up code into smaller chunks for ease of setting parameters and sometimes easier for maintenance. I would like to add that there is a rule for using non-correlated subquery such as this example, in which you can only return values from a SINGLE column from the inner query. That may be limiting for some.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi SuCheeTay,
Thank you for the comments, which elaborates what I wanted to say but was not able to phrase well!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Welcome to the world of SQL subqueries! IMHO these are an underappreciated part of SQL. Once you get into correlated subqueries you will find that they are a very powerful and efficient way of creating result sets from different tables and different levels of data granularity.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi SASKiwi, thanks a lot for your comments! I will get into more advanced proc sql steps such as correlated subqueries soon I guess. The first time (when I just began to learn base SAS procs) I see proc sql steps (using only the basic techniques including select, where, group by, having, order by) I was thinking they are very powerful techniques to create tables. Learning SAS is fun.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Not that it's shorter in this case, but you could also solve this using subqueries in a join like this:
proc sql;
select a.countrycode, a.indicatorname,
a.estyear/100 as estpct1 format=percent8.2,
a.estyear3/100 as estpct3 format=percent8.2,
estyear1
from
(select * from sq.globalfindex
where indicatorname="Borrowed for health or medical purposes (% age 15+)") A
inner join
(select countrycode from sq.globalmetadata
where upcase(region)=upcase("Europe & Central Asia") and
upcase(incomegroup)=upcase("High income")) B
on a.countrycode=b.countrycode
order by a.estyear1 desc;
quit;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks a lot for your comments! So this is another way to solve the question, the logic of which is: using one proc sql step to select columns from a table that has been created through an inner join (of two tables).
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
This is an example of an in-line view, which is covered in the same lesson as the subqueries in the SAS SQL class.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content