Proc SQL: No LOG Note for Missing values?

[novinosrin]

Proc SQL: No LOG Note for Missing values?

Why Proc SQL processes doesn't generate "missing values" note like that of a datastep LOG. ? Well it's indeed a good thing to not have annoying notes but I am intrigued to know what really goes in the distinction of Datastep compiler vs SQL processor. I do understand the mechanism but not these kind of intricacies I'm afraid. 

4318  proc sql;
4319  create table want as
4320  select *,height-. as test
4321  from sashelp.class;
NOTE: Table WORK.WANT created, with 19 rows and 6 columns.

4322  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


4323  data want;
4324  set sashelp.class;
4325  test=height-.;
4326  run;

NOTE: Missing values were generated as a result of performing an operation on missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      19 at 4325:12
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.WANT has 19 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.03 seconds

[Tom]

You are exercising two different pieces of code probably written by two different SAS programs years (decades?) apart.  Apparently they made different design decisions about whether to report operations on missing values in the log.

[novinosrin]

"You are exercising two different pieces of code probably written by two different SAS programs years (decades?) "

Sir @Tom  Indeed true. How did you guess that. I am sooo very impressed 🙂 Yep, There's a batch process that's been run every quarter and i was asked to take a look at it. Suddenly this thought struck me to ask you all.  Current decade users like me plus or minus(6 years) at my work place are perhaps dwelling unnecessarily into this nitpicks. Hmm 

[Tom]

SAS is not so much a language as a system of tools.  It has evolved over 40+ years with a remarkable amount of backwards compatibility. So many of the separate tools will have their own syntax and behavior.  For example the syntax for PROC SQL is based on the ANSI standard for SQL.  I believe the syntax of PROC TABULATE was based some existing language for specifying tables.  The DS2 language has been introduced in the past few years. etc.

[hashman]

@Tom:

I'd rather say it's a system of tools, each with its own language sharing certain main syntactic features with the others. E.g. every statement ends with a semicolon (inherited from PL/I), regardless of whether the "tool" is the DATA step, SQL, IML, FCMP, etc. They also share a set of common of naming conventions, arithmetic and logical operators, access to SAS functions and in/formats (which, in essence, are functions, too), some statements (BY comes to mind first), etc. TABULATE stands somewhat apart since it was implemented based on the existing TPL language (from the Bureau of Labor Statistics), so for a SAS programmer never exposed to it, its syntax, statements, and operators may look a tad foreign. 

If someone doesn't know one of the SAS tools, one wouldn't quite understand what its code does but still would know it's SAS. In fact, over a span of years, whenever a novel component would be brought into SAS, there has been a concerted effort (spearheaded by Rick Langston) to ensure that it "feels" like SAS. I guess it's part of the backward compatibility you've mentioned.

In this sense, the SAS language taken as a whole can be compared with a natural language (English, say) used in common speech as well as in specialized fields. For example, very few native English speakers would understand a professional conversation between two theoretical physicists held in English, and yet any would still know that, after all, they are speaking English rather than Swahili.   

Kind regards

Paul D.       

[SASKiwi]

There are some circumstances where PROC SQL will report on missing values. Date functions is one of these. It could be where functions are common to both the DATA and PROC SQL steps that they write the same messages.

[PGStats]

I think the design philosophy of SQL is to run anything that makes sense without complaining (including operations on missing values), and to notify only of suspicious operations that impact performance.

Data step programming, on the other hand, is closer to the core SAS philosophy, also featured in most procs, to help the user fully understand his/her results.

[ballardw]

I suspect there might also be question of how many missing values to report between cartesian joins,  subqueries , group by and where clauses.

Not to mention the cases of multiple nested joins.

[ChrisNZ]

The number of observation read from each input table (regardless how many times referenced, just like the data step does) would be very useful though.