topic Re: Hierarchical Queries in SAS DI Studio in SAS Data Management

Hierarchical Queries in SAS DI Studio

strsljen — Mon, 19 Mar 2018 19:13:59 GMT

Hi,

I have a table with FAR_ID and REFERRED_FAR_ID.
I need to update every FAR_ID with most recent REFERRED_FAR_ID

It can be done in first iteration, but it can also require several rounds until most recent is found

For example:

FAR_ID     REFERRED_FAR_ID
1               3
3               5 
5               6

The result I need is:

FAR_ID     REFERRED_FAR_ID
1               6

Oracle SQL code that does the job is:

SELECT
      CONNECT_BY_ROOT FAR_ID AS OLD_FAR,
      FAR_ID                 AS NEW_FAR,
      ADRESSE_ID             AS ADDRESS_ID,
      ADDRESS_STATUS,
      current_date AS LAST_UPDATE
FROM
      FAR_TABLE
WHERE
      REFERRED_FAR_ID = 0 CONNECT BY NOCYCLE PRIOR REFERRED_FAR_ID = FAR_ID

Is there a simple way of setting this in SAS DI Studio?

Thanks in advance!

Best regards,

Re: Hierarchical Queries in SAS DI Studio

LinusH — Tue, 20 Mar 2018 07:12:59 GMT

I vet a bit confused by the term "most recent".
Can you give some more examples?
Or should we assume the lowest value in the tree (can it be just one)?

Re: Hierarchical Queries in SAS DI Studio

Patrick — Tue, 20 Mar 2018 07:59:56 GMT

@strsljen

If both your source and target table reside in Oracle and given that the Oracle SQL syntax extension makes this task so beautifully simple: I'd go for explicit pass-through SQL and I'd implement without hesitation via user written code or if used several times via a custom transformation. I'd still map on metadata level source to target to support metadata level data lineage and impact analysis.

How much of the generated mapping information you then actually use in your code will depend on how much effort you want to invest. If it was me: For user written code I'd only use the libref metadata based, for a custom transformation I'd consider to implement as metadata driven as possible but make the call to what length I'm going based on how many times I'd expect to use the custom transformation and how much change to mapping/my DIS job I expect in the next 3 years or so.

Re: Hierarchical Queries in SAS DI Studio

strsljen — Wed, 21 Mar 2018 11:43:44 GMT

Hi,

I plan to do so if there is not other (clever) way.

This is only one of the use-cases I have in regards to hierarchical queries which can be fairly easily done in Oracle, but I am missing the method in SAS DI.

Re: Hierarchical Queries in SAS DI Studio

Patrick — Wed, 21 Mar 2018 11:58:47 GMT

@strsljen

There isn't an OOTB DIS transformation which generates such Oracle SQL for you. You can always create your own reusable custom transformation though (will require good SAS macro coding skills) or you just go for user written code.

I can think of ways how to program this in SAS syntax but nothing will be as simple as what Oracle offers with it's ready made syntax for such use cases - unless there is some SAS procedure I don't know of in the network analytics space which could be used for this.