Using sql subquery:thoughts and what I learnt from a practice question of SQL1:Essentials

[dxiao2017]

(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;

[SuCheeTay · Accepted solution]

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.

[dxiao2017]

Hi SuCheeTay,

Thank you for the comments, which elaborates what I wanted to say but was not able to phrase well!

[SASKiwi]

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.

[dxiao2017]

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.

[quickbluefish]

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;

[dxiao2017]

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

[SuCheeTay]

This is an example of an in-line view, which is covered in the same lesson as the subqueries in the SAS SQL class.

[dxiao2017]

BTW: as for the quote() function that creates the quoted character string to be used in the where in () statement, the reference code is on p319 of SQL1:Essential PDF, part c of question s106s02.