A tiny mistake in practice question 2, p314, SQL1: Essential course note PDF

[dxiao2017]

There is a tiny mistake in the question (s106s02, part b), on page 314, the values in the CASE WHEN statement (see below) should also include No Change, besides Increasing, Decreasing, and Unknown, according to the answer code on page 319 (see below).

 

[CarleighJoC · Accepted solution]

Great catch! After some investigation it seems like that may have been added in the case of EstYear1PCT being equal to EstYear3PCT. There was no change to the results when I deleted else "No change", but perhaps No change would appear for another value of the RegionValue macro variable- or it was just added as a "catch all". We will look into updating the instructions, thanks!

[dxiao2017]

Hi Carleigh, thanks a lot for your reply!

What I get from your reply is: for the selected value of &Regionvalue macro variable, there is no change between EstYear1PCT and EstYear3PCT; but for other possible values of &Regionvalue macro variable, maybe there is change from EstYear1PCT to EstYear3PCT, and this is the reason that "No Change" was not a value for the "Forecast" column in the question, but it appears in the answer code. Thanks a lot for your explain!

The reason I posted my thread is: while following the instruction in the question I was a bit confused (because I expected there was a chance of "no change" when comparing two numeric values, also I ignored the instruction that "Unknown" was for null values and did not know which column has missing values in the input dataset), and I wrote my answer like this (see below), I tried twice and did not get it right, then I scrolled to the answer code and see there is a "No Change" value for the Forecast column. I was going to explore the input dataset further but did not want to spend too much time on just one question, so I posted the problem here. Next time when practice this question I will explore the input dataset more. Thanks a lot for your attention and explanation! Best

proc sql;
select countrycode,indicatorname,
       estyear1/100 as estyear1pct format=percent7.2,
       estyear3/100 as estyear3pct format=percent7.2,
       case when calculated estyear3pct-calculated estyear1pct>0
               then 'Increasing'
            when calculated estyear3pct-calculated estyear1pct<0
               then 'decreasing'
            when calculated estyear3pct-calculated estyear1pct=0
               then 'Unknown'
       end as forecast
   from sq.globalfindex
   /*more statement*/;
quit;
proc sql;
select countrycode,indicatorname,
       estyear1/100 as estyear1pct format=percent7.2,
       estyear3/100 as estyear3pct format=percent7.2,
       case when calculated estyear3pct>calculated estyear1pct
               then 'Increasing'
            when calculated estyear3pct<calculated estyear1pct
               then 'decreasing'
            else 'Unknown'
       end as forecast
   from sq.globalfindex
   /*more statements*/;
quit;