clarification required

New Contributor
Posts: 3

clarification required


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.


%let banames="";
        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
    %let flg=0;
                %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;";
/*                        %end;*/

 %mend ba_init;

Super User
Super User
Posts: 7,720

Re: clarification required

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:

DEF abc

which may not be valid if you want:


Ask a Question
Discussion stats
  • 1 reply
  • 2 in conversation