BookmarkSubscribeRSS Feed
RobK777
Calcite | Level 5

Hello,

We are evaluating DI Studio (v 4.4) capabilities.  Specifically, the Import SAS Code process.

Client would like to investigate the possibility of importing BASE code into DI to automatically generate a job and create metadata.  We are interested in the following:

  • Is it possible to automatically create/register source and target DB2 tables within the job
  • Is it possible to automatically create mappings within transformations

I understand that this may be a lot to ask for from the tool.  Any suggestions/observations are welcomed.

Regards,

Rob

4 REPLIES 4
Quentin
Super User

Hi,

I haven't tried it, but looks to me like #1 should be possible.  A DI job can invoke user-written SAS code.  As I read the docs, if you have a metadata library pointing to the DBMS, you can run PROC METALIB to register all the tables in the database.

HTH,

--Q.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Patrick
Opal | Level 21

Using DIS4.6 I've done some testing importing DIS generated SAS code (after changing the name of the target table). What I found:

- I've got working DI jobs for most of the .sas code

- Libraries used need to be pre-registered

- The new target table got automatically created in metadata as permanent table (based on the column definitions in the code).

- I had issues with a few programs where I'm re-defining libraries dynamically within the code for context specific execution.

- The DI jobs generated worked but the flows consisted of nodes called "Datastep" and the flow was not very desciptive on showing what's going on in the job.

- External tables registered in metadata didn't get added to the flows (eventually because the path definitions for these external files were SAS macro variables). The import step got added as code so the resulting DI job as such was working correctly.

- Some column mapping had been added to nodes but it wasn't a clean end-to-end mapping (also because external tables hadn't been added).

Conclusion:

- "importability" of .sas files and quality of resulting flows will strongly depend on structure and code quality of source .sas files.

- assign libraries in metadata using librefs as in code.

- register tables before importing the SAS programs (so that table metadata gets created with all attributes and not only what has been defined in code).

- consider re-working the DI jobs created to get more descriptive (= better maintainable) flows and to add full end-to-end mapping from source to target (=allowing for column level impact analysis).

LinusH
Tourmaline | Level 20

In addition I would like to pint a current defect, the import will not identify if a source table has PK/FK on them, leading to that this metadat will be rewritten, and probably leading to a Java error during the import.

Another thing is to make sure that intermediate work tables should have individual names, reusing a table name will make DIS confused, or at least could make the flow look weird.

Macro looping can work, but if you have call execute functionality, this will probably have an unwanted layout in the DIS flow.

Data never sleeps
RobK777
Calcite | Level 5

Thanks for the input, greatly appreciated.

We found limitations even when using DI generated code.  When importing back into DI, it would not produce the same results.

Libraries and input tables were pre-registered.  DI did not register/recognize input DB2 tables only register output SAS tables.

Obviously this affected mappings and subsequently lineage capabilities.

Patrick, point taken about "quality" of source code.  I feel that the sample code I'm working with is clearly structured and well written.  No complex macros or looping involved.

Kind regards,

Rob

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
  • 1096 views
  • 0 likes
  • 4 in conversation