02-01-2017 08:08 AM
We are executing the below code to creating some temporary libraries dinamically(L1,L2....)
we are able to see the libraries but we are didnt find any data under that libraries.
can you please provide some code to extract the datasets/views it to that libraries.
proc sql noprint;
SELECT cats("'", TARGET_BA_SCHEMA_NAME, "'")as BA_SCHEMA_NAME,
BA_OBJ_ID format=15., BA_OBJ_VER, BA_COMPANY_ID,
SAS_LIB_NAME,DATA_SENSITIVITY,cats("'",SNAPSHOT_LABEL,"'") as snapshot_label
into :banames separated by
'#', :baobjid separated by '#', :baobjver separated by '#',
:bacompid separated by '#', :libnm separated by '#',
:dsblind separated by '#',:snplbl separated by '#' FROM ba_dtl WHERE
process_log_id in (SELECT MAX(process_log_id) FROM ba_dtl
GROUP BY target_location, archival_folder) and sas_lib_name is not null;
%let rwcnt = %sysfunc(countw(&banames, "#"));
%if &rwcnt >=1 %then
%do i=1 %to &rwcnt.;
%let compid = %scan(&bacompid, &i., "#");
%let objid = %scan(&baobjid, &i., "#");
%let objvr = %scan(&baobjver, &i., "#");
%let baname = %scan(&banames, &i., "#");
%let snaplbl = %scan(&snplbl, &i., "#");
%let lbnme = %scan(&libnm, &i., "#");
%put &baname &snaplbl &dsbln;
LIBNAME &lbnme oracle USER=&usr PASS="&pwd" PATH=&dbname
dbconinit="BEGIN Pkg_Business_Area.SP_Initialize_BA(&BAName,&snaplbl,&dsbln); END;";
02-01-2017 08:36 AM
I can think of two possibilites:
1) The user details provided don't have access to those schemas.
2) The code which is being generated is not correct, maybe spaces or something like that.
Now 1 can be checked easily byt doing a simple proc sql select * from connection to oracle ... just select from something you hard code in for a run.
Number 2 however is not something we could chek from what is provided. You need to look at what code is generated from this macro, take some of the log where macro is de-referenced. Then run look at this and see if it is correct and points to an existing area.
Using macro variables you can find things like spaces creep in, for example:
If BAName has a trailing blank and snaplbl is abc, then you might see:
which may not be valid if you want: