(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;
... View more
