Thought I was ok with pass-thru SQL, but trying to understand: The WEEKDAYCD='' WITH UR clause? (Presumably some DB2-specific syntax I haven't seen before) What the X(DT) does? (Is this passed-through, so DB2-specific? Or a SAS function? Is it an SQL variant of the 'X' command?) Thanks. PROC SQL; CONNECT TO DB2(SSID=DSN); CREATE TABLE MYWORK AS SELECT * FROM CONNECTION TO DB2 (SELECT MAX(BIRTH_DT) FROM CUST.CALENDAR WHERE BIRTH_DT < CURRENT DATE AND WEEKDAYCD='' WITH UR)X(DT); DISCONNECT FROM DB2; QUIT; ... View more

PROC SQL - if there is no "ORDER BY", does PROC SQL retain row order when using SET & UNION operators?   e.g, proc sql; insert into table1 select a, b, c, d from table2; create table table1 as select * from table1 union all select * from table2; quit; (Yes, if it were me I'd put an "ORDER BY" clause, but we're trying to set up regression testing for some existing code.)   I'd assumed row order would be retained, but then read the following:   SUGI 31: 242-31 "SQL SET OPERATORS: SO HANDY VENN YOU NEED THEM", Howard Schreier, Howles Informatics. "Since there is no ORDER BY clause in the SQL code, the SQL processor is not obligated to deliver its results in any particular order. The order we observe is basically a consequence of internal optimization; the processor is avoiding unnecessary work by simply preserving the order in which it encounters the rows."   I've searched for clarifying documentation but cannot find any.   Is there definitive documentation somewhere clarifying returned row order if there is no "ORDER BY" clause?   Thanks, Andrew.   ... View more

Trying to make sense of the "D" numeric format, and when best to use it.   And why a "put(12345,D32.9)" yields "          12345.0000000000000000" (16 zeroes)?   Why use D32.9 when I could use 32.16?   The documentation seems a bit hard to decypher (summary below).   Any assistance appreciated - use cases, perhaps?   Dx.p format - SAS(R) 9.4 Formats and Informats: Reference   Prints numeric values, possibly with a great range of values, lining up decimal places for values of similar magnitude.   Details - The Dw.p format writes numbers so that the decimal point aligns in groups of values with similar magnitude. Larger values of p print the data values with more precision and potentially more shifts in the decimal point alignment. Smaller values of p print the data values with less precision and a greater chance of decimal point alignment.   Comparisons The BESTw. format writes as many significant digits as possible in the output field, but if the numbers vary in magnitude, the decimal points do not line up. Dw.p writes numbers with the desired precision and more alignment than the BESTw format. The BESTDw.p format is a combination of the BESTw. format and the Dw.p format in that it formats all numeric data, and it does a better job of aligning decimals than the BESTw. format. The w.d format aligns decimal points, if possible, but it does not necessarily show the same precision for all numbers. ... View more