BookmarkSubscribeRSS Feed
svellaichamy
Fluorite | Level 6

I have SAS 9.3 Data Integration Studio. I have a task to produce a report on source and target tables/columns for the list of source table names. For example, if I have source table ST1 with columns ST1C1, ST1C2, ST1C3 and this table is used to create table J1TT1 with columns J1TT1C1, J1TT1C2, J1TT1C3 in Job1 and the same table is used to create another table J2TT1 with columns J2TT1C1, J2TT1C2, J2TT1C3 in Job2 and my report should be as follows. I can get this information in SAS DI Studio by right click on Analyze and then Analyze columns (i.e, Impact analysis of table/column) but not in table report format. I need the result in table format as follows. Could someone please help on this?

 

Source_Table Source_Column Target Table Target Column

ST1                ST1C1                J1TT1           J1TT1C1    

ST1                ST1C2                J1TT1           J1TT1C2    

ST1                ST1C3                J1TT1           J1TT1C3    

ST1                ST1C1                J2TT1           J2TT1C1    

ST1                ST1C2                J2TT1           J2TT1C2    

ST1                ST1C3                J2TT1           J2TT1C3  

 

 

10 REPLIES 10
AndrewHowell
Moderator

Have you thought of trying the Relationship Report in the $SASHOME/SASPlatformObjectFramework folder?

 

The documentation is in the SAS 9.4 Intelligence Platform: System Administration Guide.

svellaichamy
Fluorite | Level 6

Hi AndrewHowell, Thanks for your response. I have SAS 9.3 and looks like Relationship Report Facility is not available in SAS 9.3. Could you please let me know any other way to produce the required report in SAS 9.3

AndrewHowell
Moderator
Upgrade? (Sorry, couldn't resist..)
AndrewHowell
Moderator

Ok, I haven't had access to a 9.3 platform for some time now, so I can only go by the equivalent SAS 9.3 online documentation..

 

So it looks like the same batch-file functionality doesn't exist in v9.3.

 

However, there is still the possibility of constructing your own metadata lineage report by running either XML requests, or SAS-coded metadata calls to find the attributes/associations of specific fields in specific tables.

 

Documentation is in the SAS(R) 9.3 Language Interfaces to Metadata

 

Hope that helps..

 

Regards,

Andrew.

svellaichamy
Fluorite | Level 6

I have tried with PROC METALIB but could not able to achieve the column impact analysis (available in DI) in program mode using PROC METALIB to get the results.

mohd-danshaik
Fluorite | Level 6

Hi Guys,

I am also currently working on a case study where in I need to extract the impact analysis details for any given table in SAS DI.

 

My question is pretty similar to the original question in this post but I would only want to check the data lineage for any given source table using SAS code.

 

I am currently using SAS 9.4

AllanBowe
Barite | Level 11

You can extract this information using a recursive call to the metadata to explore the tree.

 

We have some documentation on it here:  https://docs.datacontroller.io/dcu-lineage/

 

Advanced Data Lineage exploration is available at both table and column level in the SAS 9 version of Data Controller.  You can export in PNG, SVG and CSV (table) formats.  The tool is compatible from 9.3 onwards. 

 

 

Column level version (interface is slightly different as the video is from an earlier version of the product).  The CSV export is not shown but it's the same as the table-level one above, with additional columns for the..  columns.

 

 

A free version is available and we'd be happy to discuss - the contact form is here:  https://sasapps.io

/Allan
SAS Challenges - SASensei
MacroCore library for app developers
SAS networking events (BeLux, Germany, UK&I)

Data Workflows, Data Contracts, Data Lineage, Drag & drop excel EUCs to SAS 9 & Viya - Data Controller
DevOps and AppDev on SAS 9 / Viya / Base SAS - SASjs
mohd-danshaik
Fluorite | Level 6
Hi Allan,
Thanks a lot for your response. We do not have the Data Lineage exploration available to us. So that is why we are probably trying to get it done with code or some combination of Code plus VA exploration.
Rama_V
Obsidian | Level 7

Hi,

 

I have done something similar work in the past and presented the results on SAS VA. I have parts of solution you need stitch together to make it make work.

 

Job List from Metadata.sas - List all the jobs stored on SAS metadata created using DI Studio.

Job Source and Target Table.sas - List the tables used as source and Target table. You need to send dynamically the job uri from job list and get the data for every job. Also need modification to add Source and Target URI. Refer Base SAS and Metadata browser to understand the structure.

Table column inforamation.sas - This will give column information for all the tables on Metadata, so you need to control using Table URI from above.

 

Integrate all the data and present using any reporting tool. Can use exploration on VA to present impact analysis for lineage?

 

 

Hope this helps.

Rama

 

mohd-danshaik
Fluorite | Level 6
Hi Rama_V,

Thanks a lot for your inputs. This could be really helpful. I had thought of a similar way of extracting meta info and then representing them in SAS VA.
Will implement the same and keep you posted if it works

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
  • 10 replies
  • 2554 views
  • 4 likes
  • 5 in conversation