BookmarkSubscribeRSS Feed
strsljen
Obsidian | Level 7

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,

--
Mario
4 REPLIES 4
LinusH
Tourmaline | Level 20
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)?
Data never sleeps
Patrick
Opal | Level 21

@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.

strsljen
Obsidian | Level 7

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.

--
Mario
Patrick
Opal | Level 21

@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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 1165 views
  • 0 likes
  • 3 in conversation