AndrewHowell Tracker

DB2 pass-through SQL - unknown functions/clauses?

AndrewHowell — Wed, 22 Feb 2023 04:57:13 GMT

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;

Re: PROC SQL row order

AndrewHowell — Wed, 12 Feb 2020 03:56:12 GMT

Thank all for the responses.

To clarify - yes, the new code will be better & tighter, but we are trying to set up some regression testing on the existing (less than stellar) code.

Cheers.

PROC SQL row order

AndrewHowell — Tue, 11 Feb 2020 23:04:46 GMT

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.

Re: Dx.p format -

AndrewHowell — Thu, 03 Oct 2019 09:45:10 GMT

@ChrisNZ,

I've tried 32.0 up to 32.9 (32.0 is same as 32.3). The result is still MORE decimals than

So I guess I'm looking for clear documentation on what the "D" format is actually FOR, and how to know - with certainty - what the output would be for various values of width and/or decimal.

Background: I'm storing the result in strings, and I need to know the length of the strings and the placement of the decimal point.

Dx.p format -

AndrewHowell — Wed, 02 Oct 2019 14:22:41 GMT

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.

Re: SAS Global Forum 2020 - Need some help from the community.

AndrewHowell — Thu, 09 May 2019 02:44:19 GMT

Hi Margaret.

Given a lot of existing customers are still on the 9.x platforms, and SAS is advocating Viya, I would strongly recommend a "Migration" section, consisting of several (many?) topic on the challenges faced by administrators when migrating.

Regards,

Andrew.

Re: SGF Saturday night - where to meet?

AndrewHowell — Mon, 29 Apr 2019 19:50:11 GMT

No more SAS #tweetup sessions..?? [UnLike!]

Re: SGF Saturday night - where to meet?

AndrewHowell — Sat, 27 Apr 2019 13:47:53 GMT

Sounds like a plan, @MichelleHomes - see you there.

Re: SGF Saturday night - where to meet?

AndrewHowell — Fri, 26 Apr 2019 00:44:03 GMT

Thanks @CJac73 , but what I meant was whether the collective SAS hive mind had formulated some sort of gathering somewhere..

SGF Saturday night - where to meet?

AndrewHowell — Thu, 25 Apr 2019 10:49:01 GMT

Ideas for a catch-up Saturday night in Dallas?

(Saw the Stravinsky suggestion - considering it, just seeing what else is happening..)

Re: What is the best way to get around Dallas?

AndrewHowell — Thu, 25 Apr 2019 10:36:08 GMT

@schatja - links to the app are in the "Get Ready" section at the bottom of https://www.sas.com/en_us/events/sas-global-forum/attend/travel.html

Re: What is the best way to get around Dallas?

AndrewHowell — Thu, 25 Apr 2019 07:00:23 GMT

There are DART disruptions on the weekend, which will affect anyone coming into town from DFW on the Orange Line.

I've posted info here.

@gmbaker - might be worth adding a note to the travel section in the SGF app (which I haven't got around to downloading yet..)?

Re: You TOO can earn communities swag! Here’s how

AndrewHowell — Tue, 23 Apr 2019 12:27:56 GMT

Badge checklist:
- "To The Rescue" - tick!
- "Fire Starter" - tick!
- "First Accepted Solution" - tick!
- "Regular Attendance 2019" - tick!
- "Motivator" - tick!
Recent activity - almost every day, so hopefully tick!

Re: Picking up Every Second Last Observation

AndrewHowell — Mon, 08 Apr 2019 04:02:26 GMT

Are I & T fixed at (say) 500 & 10, respectively?

If so, then just filter the top 3 values of T (8, 9, 10)?

data temp1;
	set temp;
	where t in (8,9,10);
run;

Or if this is oversimplifying, then try (something like) this:

data temp1;
	set temp;
	by i t;
	OnePrev=lag1(y); TwoPrev=lag2(y);
	if last.i then output;
run;

Re: Design question SAS 9.4/VA 7.3 - Reload-On-Start / AutoLoad of non-SAS data

AndrewHowell — Thu, 21 Mar 2019 22:45:44 GMT

Hi Juan, hope you are well.

Thanks for your response - it confirms what I suspected (but was hoping for something better from someone who knew more than me).

I also have a ticket logged with SAS Technical Support, so I'll see if they come up with anything.

Regards,

Andrew.

Design question SAS 9.4/VA 7.3 - Reload-On-Start / AutoLoad of non-SAS data

AndrewHowell — Wed, 20 Mar 2019 23:22:57 GMT

Not getting any nibbles in the VA community, so thought I'd try here for a design perspective..

SAS v9.4 (Linux) & VA v7.3 on a 6-node Cloudera Big Data Appliance (BDA).

Looking to better understand loading options loading Hadoop data (Hive) into LASR libraries - Reload-On-Start, AutoLoad, scheduling, etc.

Preferences:

Stored data is Hive, so that other software (not just SAS VA) can access it.
Load the data from disk directly to in-memory LASR, without being piped via an external SAS server running SAS code.
Avoid storing SAS-native copy of the data - SASHDAT (for Reload-On-Start) or SASBDAT (for AutoLoad).

Q's:

What Reload/AutoLoad capabilities do we have with Hive data?
If we HAVE to make a (SAS-specific) copy of the data, would appreciate recommendations for Reload / AutoLoad.

Thanks.

Re: Single vs doublr trailing @:s?

AndrewHowell — Sun, 17 Mar 2019 23:19:39 GMT

A SINGLE trailing "@" holds the input buffer only for the current iteration of the data step (i.e, til processing returns to the top of the data step), or is passed a subsequent INPUT statement without a trailing "@".

A DOUBLE trailing "@" holds the input buffer indefinitely (i.e, multiple iterations of the data step) as long as there is data in the input buffer to read.

Hope that helps?

Re: SAS clients on Apple Mac?

AndrewHowell — Thu, 06 Dec 2018 03:29:33 GMT

@SimonDawson - thanks, figured that may be the answer.

I had hoped for either:

ALL Java clients to be able to be run from X, or;
At the vary least, the Java source was included in the deployment to able to compile if required.

Oh well.

Re: SAS clients on Apple Mac?

AndrewHowell — Thu, 06 Dec 2018 00:15:26 GMT

@SimonDawson - thanks for the comprehensive response.

Any reason why only SAS Management Console, and not the other Java clients (esp DI Studio)?

Re: SAS clients on Apple Mac?

AndrewHowell — Thu, 06 Dec 2018 00:14:23 GMT

@ronan - yes, concur thick clients require X. I'm looking for the next extension of that to clarify what SAS Java client apps can/can't be run on MacOS using X.

SAS Management Console is available to launch: $SASHOME/SASManagementConsole/9.4/sasmc (I use it often.)

What about running the other Java client apps (SAS Data Integration Studio, in particular) on MacOS using X?

For example, I can't find an equivalent distudio executable in the $SASHOME folder - does this mean is just has to be compiled, can it be compiled, or is there a critical reason it's not there which would render my search futile?