Capture SQL View Definition for Programmatic Change

[Patrick]

Hi Experts,

I need to replicate a SAS 9.4 data environment in a Linux environment. Using rsync -av works like a charm for this.

The challenge I'm facing is that the source environment also contains SQL views in the form:

select *
from lref.table
using libname lref "<source_root_path>/<relative path>";

I need to re-create these views in the target environment by replacing the source_root_path with the target_root_path.

Do you know of any other/better way than executing a SQL describe view, capture the log output in a file, parse out the view definition, change the path and then use this new code to create the views in the target environment?

I couldn't figure out any other way to retrieve the view definition.

Thanks, Patrick

[Kurt_Bremser]

UNIX systems have very nice tools for exactly such mass text change operations, e.g. awk. Just run that on the original source codes of the SQL views. If those source codes are not available, someone fucked up big time (see Maxim 27).

[Patrick]

Hi @Kurt_Bremser 

Thanks for answering. 

There are no explicit scripts. The views get created metadata driven. Below an example of the main "table" for this.

What I'm trying to do, is to create the data environment for an integration environment using the data from source that has been defined as "source of truth" and against which data needs to get reconciled (after running for another month).

In theory I wouldn't have to bother about the views, because they should get re-created by such a run. ...but one can't be sure.

I could of course use the config Excels and the existing scripts from source to build some adhoc job for re-creating the views in the target environment, BUT... I really would like to avoid this. Things are done messy. 
What I would like to do, is to create the data (views included) for this integration environment without any dependency on any version of Excel config sheets or scripts.

I hope this explains why I'm trying to extract and use the view definitions to re-create the views. 

Regards, Patrick

[Quentin]

AI had a reasonable suggestion:

In your target environment, could you create a symlink that points  source_root_path  to target_root_path?

That would avoid code changes.

AI also generated a macro to implement the log capture approach you mentioned.  This is #AI and #UNTESTED, but from a quick skim read doesn't look crazy.  The AI was running this on windows SAS, not linux, so would likely need to be refined... 

%macro export_sql_views_with_path_map(
  lib=,
  outcode=,
  source_root=,
  target_root=,
  execute=no,
  include=,
  exclude=
);
  %local execute_uc view_count;

  %let execute_uc=%upcase(%superq(execute));

  %if %superq(lib)= %then %do;
    %put ERROR: (export_sql_views_with_path_map) LIB= is required.;
    %return;
  %end;

  %if %superq(outcode)= %then %do;
    %put ERROR: (export_sql_views_with_path_map) OUTCODE= is required.;
    %return;
  %end;

  %if %superq(source_root)= %then %do;
    %put ERROR: (export_sql_views_with_path_map) SOURCE_ROOT= is required.;
    %return;
  %end;

  %if %superq(target_root)= %then %do;
    %put ERROR: (export_sql_views_with_path_map) TARGET_ROOT= is required.;
    %return;
  %end;

  %if &execute_uc ne YES and &execute_uc ne NO %then %do;
    %put ERROR: (export_sql_views_with_path_map) EXECUTE= must be YES or NO.;
    %return;
  %end;

  proc sql noprint;
    create table work._esv_views as
    select libname
          ,memname
    from dictionary.tables
    where libname = upcase("%superq(lib)")
      and memtype = 'VIEW'
      %if %superq(include) ne %then %do;
      and upcase(memname) like upcase("%superq(include)")
      %end;
      %if %superq(exclude) ne %then %do;
      and upcase(memname) not like upcase("%superq(exclude)")
      %end;
    order by memname
    ;

    select count(*)
      into :view_count trimmed
    from work._esv_views
    ;
  quit;

  %if &view_count = 0 %then %do;
    %put WARNING: (export_sql_views_with_path_map) No views matched in LIB=&lib..;
    %return;
  %end;

  filename esvdesc temp lrecl=32767 encoding='utf-8';
  filename esvdrv  temp lrecl=32767 encoding='utf-8';
  filename esvout  "%superq(outcode)" lrecl=32767 encoding='utf-8';

  data _null_;
    file esvdrv;
    put 'proc sql;';
    set work._esv_views end=last;
    put '  describe view ' libname '.' memname ';';
    if last then put 'quit;';
  run;

  proc printto log=esvdesc new;
  run;

  %include esvdrv;

  proc printto;
  run;

  data work._esv_view_lines;
    length raw $32767 line $32767 view_name $41 libname $8 memname $32;
    infile esvdesc lrecl=32767 truncover;
    input raw $char32767.;

    raw = prxchange('s/\x0c//o', -1, raw);
    raw = tranwrd(raw, '0D'x, ' ');

    retain capture 0 seq 0 view_name libname memname;

     if scan(strip(raw), 1, ' ') = 'NOTE:'
       and scan(strip(raw), 2, ' ') = 'SQL'
       and scan(strip(raw), 3, ' ') = 'view'
       and upcase(scan(strip(raw), 5, ' ')) in ('IS', 'HAS')
    then do;
      view_name = scan(strip(raw), 4, ' ');
      libname = scan(view_name, 1, '.');
      memname = scan(view_name, 2, '.');
      capture = 1;
      seq = 0;
      return;
    end;

    if capture then do;
      if prxmatch('/^\s*\d+\s+The SAS System\b/i', raw) then return;

      line = raw;
      seq + 1;
      output;

      if strip(raw) = ';' then do;
        capture = 0;
      end;
    end;

    keep libname memname view_name seq line;
  run;

  proc sql noprint;
    select count(*)
      into :view_count trimmed
    from work._esv_view_lines
    ;
  quit;

  %if &view_count = 0 %then %do;
    %put ERROR: (export_sql_views_with_path_map) No DESCRIBE VIEW text was captured.;
    %return;
  %end;

  proc sort data=work._esv_view_lines;
    by view_name seq;
  run;

  data _null_;
    if fexist('esvout') then do;
      rc = fdelete('esvout');
      if rc ne 0 then put 'ERROR: (export_sql_views_with_path_map) Unable to replace existing output file.';
    end;
  run;

  data _null_;
    set work._esv_view_lines end=last;
    by view_name seq;

    file esvout;

    if _n_ = 1 then do;
      put '/* Generated by %export_sql_views_with_path_map */';
      put 'proc sql;';
    end;

    length rewritten $32767;
    rewritten = tranwrd(line, symget('source_root'), symget('target_root'));

    if index(symget('target_root'), '/') then do;
      rewritten = tranwrd(rewritten, '\', '/');
    end;

    if first.view_name then do;
      put;
      put '  create view ' view_name ' as';
    end;
    put rewritten;

    if last then put 'quit;';
  run;

  %if &execute_uc = YES %then %do;
    %include esvout;
  %end;

  %put NOTE: (export_sql_views_with_path_map) Processed &view_count lines for LIB=&lib..;
  %put NOTE: (export_sql_views_with_path_map) Output written to %superq(outcode).;
%mend export_sql_views_with_path_map;