Another tiny mistake in SQL1: Essentials Course Note PDF - on page 107, in practice s102s03's answer

[dxiao2017]

Hello,

I think there is another tiny mistake in the answer for practice s102s03, on page 107 in SQL1: essentials course note PDF (screenshot is as below). The merchant name of December's first transaction is Sceneit Cinemas, LLC, not Big Burgers, Inc. I run both my code and code provided by the course note and had the same results (see below).

 

title "November/December Transactions";
proc sql;
select customername label='Customer Name',
       merchantname label='MerchantName',
       amount label='Transaction Amount'
                    format=dollar10.2,
       datepart(datetime) as transactiondate
                    label='Transaction Date'
                    format=date9.
   from sq.transactionfull
   where month(calculated transactiondate)in(11,12) and
         service ^= 'University'
   order by datetime;
quit;
title;

 

title "November/December Transactions";
proc sql;
select customername label='Customer Name',
       merchantname label='MerchantName',
       amount label='Transaction Amount'
                    format=dollar10.2,
       datepart(datetime) as transactiondate
                    label='Transaction Date'
                    format=date9.
   from sq.transactionfull
   where month(datepart(datetime)) in (11,12) and
         service ^= 'University'
   order by datetime;
quit;
title;

Also, I can see from the first five rows that the above result table (see below, the lower one) is the same as the partial result table provided with the original question on course note PDF, page 65 (see below, the upper one).

[Tom]

Your picture is showing at least six observations for 01DEC2018.  Why are you picking out the first one instead of one of the others?  Remember that if you don't tell SQL how to order tied values then there is no assurance what order the tied observations will appear. 

Why are you displaying DATEPART() values, but ordering by the full DATETIME values?

That might cause the order the be different.

[dxiao2017]

Hi, the reason I picked out the first one was: among the several observations for 01DEC2018, that one is listed the first in the output table. And this is what the question asks I think. The question was to ask what was the value of the merchant name column in the first row (as it is displayed in the result table) for 01DEC2018. The order of the observations in the output table is completely defined by two things: 1) the original order of the observations in the input table and 2) the statements in the proc sql step including the order by statement.

The reason I display the datepart() value and order by the full datetime value is: that was what the question asks. I have read the question twice and confirmed it asks to order the results by the original date value(i.e., the full datetime value). Also I have even tried ordering by the calculated date value (i.e., the datepart() value) and the results was the same (and in fact, the results should be the same regardless I order by the full datetime value or the datepart() value), if I remember it right.

[Tom]

If ordering by the DATETIME will be the same as ordering by DATE only then most likely all of the datetime values for 01DEC2018 have the same time of day part.  That is frequently the case as many database systems do not actually have a way to store DATE only values like SAS does so they store DATE values as DATETIME values with the time of day set to midnight (00:00:00).

Usually SAS will retain the original relative order of observations with the same values of BY groups. In fact PROC SORT has a option now where you can insist on it.  The language of SQL explicitly allows implementations the freedom to not do that, so I would not count on that behavior with SQL code.

Also note that PROC SQL will issue a warning when you order by a variable not include in the output.

Example:

272  proc sql;
273  select name,age+10 as ageplus10
274  from sashelp.class
275  order by age
276  ;
NOTE: The query as specified involves ordering by an item that doesn't appear
      in its SELECT clause.
277  quit;

[dxiao2017]

Hi Tom, you are right about the difference between ordering by original datetime or by calculated date value. I add the original datetime column into the output table, the difference are as follows (nevertheless the 1st record of 01DEC2018 remains the same, the merchant name of which is Sceneit Cinemas, LLC).

Order by original datetime values (codes, first five rows, and the 1st record of 01DEC2018):

title "November/December Transactions";
proc sql;
select customername label='Customer Name',
       merchantname label='MerchantName',
       amount label='Transaction Amount'
                    format=dollar10.2,
       datetime,
       datepart(datetime) as transactiondate
                    label='Transaction Date'
                    format=date9.
   from sq.transactionfull
   where month(datepart(datetime)) in (11,12) and
         service ^= 'University'
   order by datetime;
quit;
title;

Order by calculated date values (codes, first five rows, and the 1st record of 01DEC2018):

title "November/December Transactions";
proc sql;
select customername label='Customer Name',
       merchantname label='MerchantName',
       amount label='Transaction Amount'
                    format=dollar10.2,
       datetime,
       datepart(datetime) as transactiondate
                    label='Transaction Date'
                    format=date9.
   from sq.transactionfull
   where month(calculated transactiondate)in(11,12) and
         service ^= 'University'
   order by transactiondate;
quit;
title;

There is a message in the log if I do not include the order by variable in the SQL select statement, however, the message is just a NOTE (not a yellow WARNING), which is acceptable.

[Tom]

I don't have your data, but the question also includes the qualification of "documented transaction".  Is there another variable you should be filtering on that would distinguish documented from non-documented transactions?

[dxiao2017]

Hi Tom, I think the word "documented" in the question means: what has been displayed in the output table, or, the records in the output table, or, what has been produced/printed out (as a record) in the output table. The question was to ask "what is the value in the Merchant Name column in the first row for 01DEC2018 in the result table?", or "in the result table, look at the first record that has been displayed for 01DEC2018, what is the value in the Merchant Name column?" It's a problem of English and do not really has anything to do with the dataset. I guessed the meaning of the question, and I guess I am the same nationality with the person who wrote that part of the material (in our language we often use the same one word for "documented" and "recorded" in a lot of scenarios, isn't it). 

[Tom]

I do not have either the data or the questions so I cannot tell if the dataset you are supposed to query has a variable, such as status, that would indicate if something was only recorded into the database of if there was some addition documented status for the observation.

Another possible reason why the question wants you to pick a different observation is that it wanted you to sort by date (not datetime) and name.  If so then a name like Big Burgers that starts with B would come before any name that starts with an S.

Example:

data test;
  input datetime :datetime. name $30. ;
  format datetime datetime19.;
cards;
01DEC2018:08:00  Some other name
01DEC2018:12:00  Big Burgers
;

proc sql;
select datetime,name 
from test
order by datetime,name
;
select datepart(datetime) as date format=date9.,name
from test
order by date,name
;
quit;

What were the exact instructions you were supposed to follow to produce the report you are then asked to examine?

[dxiao2017]

Hi Tom,

There is no such variable as status/documented in the input dataset. The question asks to order by original datetime value only and there is no any other order by variable. You are right about the order of the observations when ordering by both time and name variables. I tested ordering by both calculated date value and merchant name, the code and result are as follows (the one comes first for 01DEC2018 is Alar Air, Inc, and then Big Burgers, Inc. the second):

proc sql;
select customername label='Customer Name',
       merchantname label='MerchantName',
       amount label='Transaction Amount'
                    format=dollar10.2,
       datetime,
       datepart(datetime) as transactiondate
                    label='Transaction Date'
                    format=date9.
   from sq.transactionfull
   where month(calculated transactiondate)in(11,12) and
         service ^= 'University'
   order by transactiondate, merchantname;
quit;

When order by original datetime value and merchant name, the one comes first for 01DEC2018 is Sceneit Cinemas, LLC again (this confirms that the earliest transaction occurred on 01DEC2018 was Sceneit Cinemas, LLC, btw):

proc sql;
select customername label='Customer Name',
       merchantname label='MerchantName',
       amount label='Transaction Amount'
                    format=dollar10.2,
       datetime,
       datepart(datetime) as transactiondate
                    label='Transaction Date'
                    format=date9.
   from sq.transactionfull
   where month(calculated transactiondate)in(11,12) and
         service ^= 'University'
   order by datetime, merchantname;
quit;

The code (both my answer and the answer provided by the course note PDF) in my first post exactly reflects the procedures the question asks to follow. I think the steps and procedures were phrased clear and not ambiguous in the question, except the "the first documented transaction" part. It would be better if the question explicitly asks to produce a report that examines what is the merchant name of the earliest transaction happened on 01DEC2018 (without showing the time part of the datetime value and display the date only).

[Tom]

What if you order by that first column, looks like Customer? within DATE?

Does Big Burgers come first then?

[dxiao2017]

Hi here you go (see below for code and results): when order by date and customer name, the 1st one for 01DEC2018 becomes Economical Superstore (and we know that no matter what is the second order by variable, if the first order by variable is the datetime value, the 1st one for 012DEC2018 will always be the cinema one which starts with S because it was the earliest transaction of that day. And therefore I insist that the answer for the question is the cinema one which starts with S, not the big burger one.

proc sql;
select customername label='Customer Name',
       merchantname label='MerchantName',
       amount label='Transaction Amount'
                    format=dollar10.2,
       datetime,
       datepart(datetime) as transactiondate
                    label='Transaction Date'
                    format=date9.
   from sq.transactionfull
   where month(calculated transactiondate)in(11,12) and
         service ^= 'University'
   order by transactiondate, customername;
quit;

[Tom]

Thanks for taking the time to explore.

Looks like there is a problem. I would assume something changed either in the data or the question and they did not double check the results before publishing.

I am actually more concerned by the code you posted in your first screen shot that was reporting DATE values generated by the DATEPART() function but sorting by the original datetime values.  That is poor coding style.  As I pointed out before that will also cause SAS to generate a message into the log noting that issue of sorting by a value that is not included in the result set.

[Cynthia_sas · Accepted solution]

Hi, Tom:

  You're right, if the student is looking at an older version of the course notes, there's a chance that the data and/or the class changed between that version of the course and the current version of the course. When I check the current e-learning version and classroom version of the SQL course notes, the correct answer for that practice is provided as Sceneit Cinemas, as shown below (problem on white background, solution on blue background):

  I'd suggest that the student take a look at the newest version of the course because typically with each release of a course, we correct errata items from the previous version and might change the practices and activities and demos based on student and instructor feedback.  That looks like what happened in this case. My guess is that the student in this case has an older PDF copy of the course notes, possibly from a SAS 9.3 or early 9.4 version of the class).

Cynthia

[Tom]

Is there any reason why the course would ask the students to order a report by a variable that does not appear in the report?

[Cynthia_sas]

 Tom:

  In reviewing the instructions for this practice in the most current version of the course (and from teaching the course) -- we cover a lot of items, such as ordering, working with functions and date/datetime variables, creating new columns and subsetting with a WHERE in this section. There are multiple lectures on each of these topics. My sense of the exercise is that it is meant to pull together multiple concepts that were taught into one practice:

  I frequently have students who get data from their databases with datetime values but they only want the date to be visible or they need to use a function on the value from DATEPART to get month or year (for example). So this practice was intended for the student to use the DateTime variable in the creation of the new column Transaction Date. I think that the original problem of the Big Burgers vs Sceneit Cinemas as the right answer did not have so much to do with the query itself, but with the version of the course and the version of the course notes. I don't have access to the older errata that was fixed for the course either, so that might have been a factor.

  Cynthia