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
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.
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
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.
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.
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
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
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
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.