Statistical Analysis System (SAS)

Connect to Statistical Analysis System (SAS) to scan metadata and display it on lineage. To import lineage metadata, create data source definition, and metadata import job.

Statistical Analysis System (SAS) is an integrated software suite for advanced analytics, business intelligence, data management, and predictive modeling.

Supported Statistical Analysis System versions

  • Statistical Analysis System (SAS) 9.4

Processed metadata

The following SAS metadata is processed and displayed on lineage:

  • SAS scripts
  • User-defined functions and procedures
  • Top-level statements
    • Libname, Option, Run, Quit statements
    • SAS/ACCESS Interface to JDBC, ODBC, ORACLE, POSTGRESQL, NETEZZA, TERADATA, DB2, MS SQL
    • Filename
    • SAS/CONNECT: Rsubmit, Signon, Signoff, Rdisplay, Rget, Waitfor, Listtask, Killtask and Proc download
  • Data step statements
    • Control flow: if, if-then-else, do-end, do-while, do-iterative
    • Statements: input, assign, length, attrib, set, merge, datalines, delete, put, keep, error, retain, rename, by, return, stop, abort, informat, infile, where, select
  • Proc step statements
    • Proc SQL statements: alter table, create index, create table/view, select (without select directly from SAS file; also the calculated columns are supported), connect to, disconnect, execute, delete, describe, drop, insert, reset, update, validate
    • Proc fcmp: function, subroutine
    • Proc freq: by, exact, output, tables, test, weight
    • Proc append
    • Proc sort
    • Proc datasets: delete, copy
    • Proc contents: delete
    • Proc export
    • Proc import
  • Macros

    • Macro variables

    • Macro methods

    • Processing macros when they are invoked from another program. The following code example shows how macro from one SAS program is used in another program. The macro extraction step extracts macros from all programs and stores them in the input service for analysis.

          main_program.sas
    ---------------------
    %setenv(env=prod); /* calling a sas macro which is defined in a seperate sas program file */
    data member_master;
    set eds.member_master; /* eds library defined in setenv.sas */
    run;
    ...
    ...
    ;

    setenv.sas
    -----------
    %macro setenv(env=);
    %IF &env.=dev %Then libname eds  "/opt/sas/data/dev/stage";
    %IF &file_name=prod %Then libname eds "/opt/sas/data/prod/stage";
    %mend setenv;

Creating a metadata import asset

Data source connection

You can import lineage metadata from Statistical Analysis System by providing external files only. You don't connect directly to the Statistical Analysis System system. It means that you need to provide a data source definition only. You can create it before you start creating a metadata import asset, or you can do it directly in the metadata import wizard.

After you select a data source definition, the connection is created automatically. You can then proceed to adding external inputs, which are required.

Data source definition

Select Statistical Analysis System (SAS) as the data source type.

External inputs

External inputs are required as this is the only way to import metadata.

To analyze SAS files, create an archive .zip file that contains SAS Programs, SAS include files and SAS metadata collection files. Each .sas file must have a unique name so that lineage is generated correctly. Folders can have duplicate names. The .zip file can have the following structure:

    "<programs>",
    "   - <sas_script1.sas>",
    "   - <sas_script2.sas>",
    "<includes>",
    "   - <include1.sas>",
    "   - <include2.sas>",
    "<extractor>",
    "   - <SAS_dataset.sas>",
    "   - <SAS_catalog.sas>",
    "replace.csv"

The replace.csv file contains placeholder replacements for the scripts that are added in the .zip file. For more information about the format, see Placeholder replacements.

SAS metadata collection files
When your program uses SAS datasets from a library that is not explicitly defined within the same program, provide the metadata from SAS datasets. Copy all SAS metadata collection for predefined library table definitions into the /extractor folder of the .zip file. Follow these steps:

  1. Download the SAS code to generate DDL statements for all the tables from all SAS permanent libraries. Download generate_metadata_table.zip.

  2. Create a libnames.txt file with all the library definitions from which you want to extract metadata. See an example content on the file:

    libname dds '/opt/sas/data/dds';
    libname dds1 '/opt/sas/data/dds1';
    libname stg1 '/opt/sas/data/stg1';
    libname stg2 '/opt/sas/data/stg2';
  1. Add the libnames.txt file to the /tmp directory. If you want to use a different location, modify line 21 in the generate_metadata_table.sas file.
    %let libloc= /tmp; /*** path where libnames.txt file will be saved *****/

If you want the output files to be added to a different location than /tmp directory, modify line 22 in the generate_metadata_table.sas file.

    %let outfile= /tmp; /*** Path where the output sas code files will be generated ****/
  1. Run the generate_metadata_table.sas file. The total number of files that are generated are the same as the number of entries in the libnames.txt file. See an example content of the lib_meta_stg1.sas file:
    libname DDS '/tmp/dds';
     
    /* SAS data step DDL for DDS.A2_ADDRESS_WITH_LABLES; */
    data DDS.A2_ADDRESS_WITH_LABLES; 
      attrib
        ADDRESS_END_DATE                 length=$8. format=22.3 label="Last date of Address validity" 
        ADDRESS_RK                       length=8  format=12.  label="ADDRESS_RK" 
        ADDRESS_START_DATE               length=$8. format=22.3 label="First day of address validity" 
        ADDRESS_STATE_CODE               length=3  format=$3.  label="ADDRESS_STATE_CODE" 
        ADDRESS_STATE_NAME               length=50  format=$50.  label="ADDRESS_STATE_NAME" 
        ADDRESS_STREET                   length=100  format=$100.  label="ADDRESS_STREET" 
        ADDRESS_TOWN                     length=50  format=$50.  label="ADDRESS_TOWN" 
        ADDRESS_TYPE_CODE                length=3  format=$3.  label="Adress Type Code (HOM=Home, WRK=Work)" 
        ADDRESS_TYPE_TEXT                length=50  format=$50.  label="Address Type (Home, Work)" 
        ADDRESS_ZIP                      length=10  format=$10.  label="ZIP code" 
        ARRIDX_POPULATION_REGISTRY_IF23  length=8  format=13.  label="ARRIDX_POPULATION_REGISTRY_IF23" 
        IDS_IF23_ADDRESS                 length=8  format=22.  label="IDS_IF23_ADDRESS" 
        POPULATION_REGISTRY_RK           length=8  format=14.  label="POPULATION_REGISTRY_RK" 
        PROCESSED_DTTM                   length=8  format=20.  label="PROCESSED_DTTM" 
        SOURCE_SYSTEM_CD                 length=3  format=$3.  label="SOURCE_SYSTEM_CD" 
        SYS_CREATEDATE                   length=8  format=9.  label="SYS_CREATEDATE" 
        VALID_FROM_DTTM                  length=8  format=20.  label="VALID_FROM_DTTM" 
        VALID_TO_DTTM                    length=8  format=20.  label="VALID_TO_DTTM" 
      ;
      stop;
    run;
     
    /* SAS data step DDL for DDS.TEST1; */
    data DDS.TEST1; 
      attrib
        col1                             length=8  format=8. 
        col2                             length=3  format=3. 
        col3                             length=4  format=4. 
      ;
      stop;
    run;
  1. Copy the generated files to the /extractor folder.

Processing %if-%then-%else macros

When an %if macro is used to construct SQL statement, the preprocessor evaluates both %then and %else branches without considering runtime conditions. As a result, invalid SQL is produced, and lineage might show an invalid flow of data. The following SQL code is missing logical operators:

select x from t where
%IF (&weekday. eq 2) %THEN
%DO;
%put&weekday.;
  (t1.AWN_DATE BETWEEN DATE -3 AND DATE -1 )
%END;
%ELSE %DO;
  t1.AWN_DATE = DATE -1
%END;
order by CHANGE_DATE;

The following invalid output is generated:

select x from t where
  (t1.AWN_DATE BETWEEN DATE -3 AND DATE -1 )
  t1.AWN_DATE = DATE -1
order by CHANGE_DATE;

To make sure that lineage is generated correctly, use the replace.csv file to remove or adjust one of the branches. For example, to correct the %else branch from the previous example of invalid code, add the following lines to the replace.csv file:

  • "t1.AWN_DATE = DATE -1","and t1.AWN_DATE = DATE -1" - this code adds an and operator to the statement so that it is properly connected.
  • "t1.AWN_DATE = DATE -1","" - this code makes the condition blank.

Learn more