topic help with question on page87-88, practice, SQL1 essential in Advanced Programming

help with question on page87-88, practice, SQL1 essential

dxiao2017 — Mon, 17 Feb 2025 10:34:08 GMT

Hi, anyone did this question and can help? I have two questions about the solution and code for the challenge practice on page87-88 of SQL1 essential pdf. (1) why do I get those brackets () for the pctincrease column in the output (see below)? I did not write any brackets() in my code. (2) in where statement, for some reason I cannot write the complete value("Borrowed for health or medical purposes (% age 15+)") of the indicatorname column (I do not think I had spelling or upcase or lowcase problems), if I write the complete value I got nothing in the output table

proc sql;
select region,
       (mean(estyear1pct)/100)*sum(estyear1pop) as estcount1
                        format=comma12.,
       (mean(estyear3pct)/100)*sum(estyear3pop) as estcount3
                        format=comma12.,
       (calculated estcount3-calculated estcount1)/calculated estcount1
                        as pctincrease format=percent7.2
   from sq.globalfull
   where indicatorname like "%health%"
   group by region
   order by pctincrease desc;
quit;

Re: help with question on page87-88, practice, SQL1 essential

Patrick — Mon, 17 Feb 2025 11:04:47 GMT

why do I get those brackets () for the pctincrease column in the output (see below)? I did not write any brackets() in my code.

You are using SAS format PERCENTw.d to print the numbers. This format prints negative numbers in brackets

for some reason I cannot write the complete value("Borrowed for health or medical purposes (% age 15+)") of the indicatorname column

The percent sign % is also a SAS macro token. If you use it as a wildcard in a SQL like expression always use single quotes so SAS doesn't treat it as macro token (if you haven't gotten to SAS Macro language yet just take this use single quotes as a rule. You'll understand later the why).
The % in a SQL like expression is a wildcard for one or multiple characters. If you want to search for the actual character % then you should escape it as done in below sample code.

The search as such for the full string works for me which makes me think that the search string you're using doesn't match the data (which includes whitespace characters like if you believe it's blank but what's stored is actually a tab).

data work.have;
  length indicatorname $60;
  indicatorname='Borrowed for health or medical purposes (% age 15+)'; output;
run;

proc sql;
  select *
  from work.have
  where indicatorname like 'Borrowed for health or medical purposes (^% age 15+)' escape '^'
  ;
  select *
  from work.have
  where indicatorname = 'Borrowed for health or medical purposes (% age 15+)'
  ;
quit;

Re: help with question on page87-88, practice, SQL1 essential

dxiao2017 — Tue, 18 Feb 2025 16:34:59 GMT

Hi Patrick, thanks very much for your quick reply, accurately solves my problem. I did not notice that in the rows that have brackets, the value of estcount3-estcount1 was negative numbers and then I was thinking I would not get any answer by searching the internet or any help documents all the way on the internet (you even don't know what key word or question you can search for, isn't it).

For the second problem, I tried both single and double quotation mark and still got nothing, and I think you are right about that the string doesn't match the data (for some reason, which need further explore and identifying), my code and the value in the input dateset are as follows.

The column and value in the input dataset is as follows.

Both single and double quotation mark work when I use that where indicatorname like %health%; statement and what I get in the result is the same as the answer provided by the question.

Re: help with question on page87-88, practice, SQL1 essential

FreelanceReinh — Tue, 18 Feb 2025 16:50:38 GMT

Hi @dxiao2017,

Isn't there a blank between "purposes" and "(% age 15+)", which is missing in your code?

Re: help with question on page87-88, practice, SQL1 essential

dxiao2017 — Tue, 18 Feb 2025 17:49:31 GMT

Hi Reinh, you are right, solved my problem! I thought I paid sufficient attention to all details but while doing this question I actually was not. The code and result is as below. Double quotation mark also works and produced the same result.

proc sql;
select region,
       (mean(estyear1pct)/100)*sum(estyear1pop) as estcount1
                        format=comma12.,
       (mean(estyear3pct)/100)*sum(estyear3pop) as estcount3
                        format=comma12.,
       (calculated estcount3-calculated estcount1)/calculated estcount1
                        as pctincrease format=percent7.2
   from sq.globalfull
   where indicatorname='Borrowed for health or medical purposes (% age 15+)'
   group by region
   order by pctincrease desc;
quit;