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;
... View more
