Solved: Re: SAS macro variable not getting resolved

Filipvdr · Posted 01-17-2020 08:00 AM

Hello, i'm trying to write a macro to reload all my tables into LASR from HADOOP.

I'm struggling with the following:

Tablelist is a table which has all the tables which are in my metadata.

I'm checking if the table in hadoop, is also still present in lasr. The result should be in to_be_loaded.

I'm sure there is a table "TABEL_A_TEST" in library CO1IT01L. But the macro variable to_be_loaded never gets resolved in my next step %if &to_be_loaded > 0 %then ...

Where is my mistake?

%macro test;
proc sql noprint;
	 select count(*) into :to_be_loaded from tablelist
	 where upcase(libref)=upcase("co1it01l") and upcase(table_name) = upcase("TABEL_A_TEST");
	run;
	quit;
%put &table exists in metadata (1/0): &to_be_loaded; 

 %if &to_be_loaded > 0 %then %do;
    LIBNAME co1it01l SASIOLA  TAG=co1it01h  PORT=10011 HOST="sasva1.al.ndis.be"  SIGNER="https://sas.test.be:443/SASLASRAuthorization" ;
	    %if %sysfunc(exist(co1it01l.TABEL_A_TEST)) %then %do;
	       %PUT ALREADY LOADED: co1it01l.TABEL_A_TEST;
	    %end;
	    %else %do;
		 proc lasr port=10011 data=co1it01h.TABEL_A_TEST add noclass
	            signer="https://sas.test.be:443/SASLASRAuthorization";
	            performance host="sasva1.al.ndis.be";
	        run;
	        %PUT RELOADED: co1it01l.TABEL_A_TEST;
	    %end;
 %end;
 %else %do;
 %PUT NOPE;
 %end;
%mend;
%test;

Tom · Posted 01-17-2020 08:49 AM

There is a timing issue if you use CALL EXECUTE() to invoke a macro.

The first issue is that if you use double quotes in the CALL EXECUTE() statement

call execute("%mymacro()");

the macro will be evaluated before the data step runs and the generated code will be included as constant text. Using single quotes will prevent the macro processor from evaluating the macro expressions.

call execute('%mymacro()');

But the second issue is a little harder to see. SAS will evaluate the macro expression while CALL EXECUTE() is passing the string to the stack to run after the current data step finishes. For simple macros this does not cause a problem (other than making a very ugly SAS log). But for a macro that changes the values of macro variables using SAS code (call symputx() calls or INTO clause in PROC SQL) and then tries to use those changed values in macro logic you have a problem. The decision about what code to generate is made BEFORE the code that sets the macro variable has had a chance to run.

That is why %NRSTR() helps. It prevents the macro from running while CALL EXECUTE() is running. Instead the call to the macro is pushed onto the stack. And the SAS log also looks nicer since it will just show the macro call instead of all of the statements the macro generates.

call execute('%nrstr(%mymacro)()');

View solution in original post

ed_sas_member · Posted 01-17-2020 08:11 AM

Hi @Filipvdr

The first part of the code seems to work.

Are you sure there no typo, i.e. TABLE_A_TEST instead of TABEL_A_TEST for example?

Could you please share a sample of input data WORK.TABLELIST with the two variables LIBREF and TABLE_NAME ? (Maybe there is a truncation of data or leading/trailing blanks?)

PaigeMiller · Posted 01-17-2020 08:16 AM

@Filipvdr wrote:

Where is my mistake?

To get more information, place this command at the start of the program, and then re-run the program. This writes very useful diagnostic information to the LOG.

options mprint mlogic symbolgen;

If you can't figure out what the problem is, then SHOW US the log by pasting it into the window that appears when you click on the {i} icon.

--
Paige Miller

Filipvdr · Posted 01-17-2020 08:23 AM

Ok here is my full code and log.

Im trying to write a script which loads everything which is available in HADOOP libraries to LASR, if the metadata is still present of the LASR table.

Problem is when i'm checking in the tablelist if the table is still present, the macrovariable to_be_loaded does not seems to getting filled/resolved.

options mprint mlogic symbolgen;

/*1. Get list of libraries which contain tables */
data hfdslibs;
 infile "/SASDATA/DataResult/temp/list_hdfs.txt";
 input hdfs $20.;
run;

proc sql noprint;
 select distinct "'" || strip(hdfs) || "'" into: listoflibraries separated by ','  from hfdslibs;
quit;
%put &listoflibraries;

/*2. Assign */
libname formats base "/SASDATA/DataResult/formats/";
options FMTSEARCH = (formats);

/* 3. Get metadata hadoop and LASR libraries */
data LibServerComp LibServerCompLASR (drop=LasrPortC);
		length uri    uriTrees    uriTree    uriUP    uriProp    uriConn    uriDom    $256
			Server    $8
			Metaserver    $50
			Metaport    VAauthport    RowNumber    4.
			Metauser    $20
			Metapass    $56
			Metarepository    $15
			Metaprotocol    $6
			VAlasrport    5.
			LasrPortC    $5
			TKGrid    $30
			SASLibName    Engine    Libref    ParentLocation    Properties    Tag    $60
			IsPreassigned    $1
			UsingPackages    Location    $512;
	nobj=1;
	n=1;
	do while(nobj >= 0);
		nobj=metadata_getnobj("omsobj:SASLibrary?@Id contains '.'",n,uri);
		if (nobj < 0) then leave;
		rc=metadata_getattr(uri,"Name",SASLibName);
		rc=metadata_getattr(uri,"Engine",Engine);
		rc=metadata_getattr(uri,"IsPreassigned",IsPreassigned);
		rc=metadata_getattr(uri,"Libref",Libref);
		RowNumber=n;
		
		if (Engine eq "SASIOLA" OR Engine eq "SASHDAT" OR Engine eq "BASE" OR Engine eq "SQLSVR") then do;
			uriTrees=' ';
			Location=' ';
			uriUP=' ';
			UsingPackages=' ';
			uriProp=' ';
			uriDom=' ';
			Properties=' ';
			Tag=' ';
			uriConn=' ';
			LasrPortC=' ';
			
			rc=metadata_getnasn(uri,"Trees",1,uriTrees);
			rc=metadata_getattr(uriTrees,"Name",Location);
			Location=catt(Location,"/");
			
			do until (rc < 0);
				uriTree=' ';
				ParentLocation=' ';
				rc=metadata_getnasn(uriTrees,"ParentTree",1,uriTree);
				rc=metadata_getattr(uriTree,"Name",ParentLocation);
				
				uriTrees=uriTree;
				
				Location=catt(trim(ParentLocation), '/', Location);
			end;
			
			rc=metadata_getnasn(uri,"UsingPackages",1,uriUP);
			if (Engine eq "SASIOLA" OR Engine eq "SASHDAT" OR Engine eq "SQLSVR") then
				rc=metadata_getattr(uriUP,"SchemaName",UsingPackages);
			
			if Engine eq "SASIOLA" then
				do;
					rc=metadata_getnasn(uri,"Properties",1,uriProp);
					rc=metadata_getattr(uriProp,"DefaultValue",Properties);
					if Properties ne LowCase(Properties) then
						put "WARNING: Libray " SASLibName " has a Server Tag " Properties " that should be in lowercase.";
					Tag=LowCase(Properties);
					Properties=catt('/',translate(LowCase(Properties),'/','.'));
					rc=metadata_getnasn(uri,"LibraryConnection",1,uriConn);
					rc=metadata_getattr(uriConn,"Port",LasrPortC);
					VAlasrport=put(strip(LasrPortC),5.);
					output LibServerCompLASR;
					end;
			if Engine eq "SASHDAT" then
				do;
					rc=metadata_getnasn(uri,"Properties",1,uriProp);
					rc=metadata_getattr(uriProp,"DefaultValue",Properties);
					if Properties ne LowCase(Properties) then
						put "WARNING: Libray " SASLibName " has a HDFS Path " Properties " that should be in lowercase.";
				output LibServerComp;
				end;		
		
		end;
		n=n+1;
	end;
run;

/* 4. get metadata tables */
data work.tablelist;
  length libref $ 8 table_name $ 32 library_id table_id $17 _uri _liburi _tableuri _packageuri library_name library_description engine preassigned hidden
    membertype table_description tablename sastablename _modified _created library_location table_location _location $256; 
  length library_created library_modified table_created table_modified 8; 
  length _IsDBMSLibname $ 1; 
  format library_created library_modified table_created table_modified datetime.; 
  label library_id="Library ID"
        table_id="Table ID"
        library_name="Library Object Name"
        library_description="Library Description"
        library_location="Library Folder Location"
        libref="Library Reference"
        engine="Library Engine"
        preassigned="Pre-Assigned Flag"
        hidden="Hidden Flag"
        table_name="Table Object Name"
        table_description="Table Description"
        tablename="Table Name"
        membertype="Member Type"
        sastablename="SAS Table Name"
        table_location="Table Folder Location"
        table_created="Table Metadata Created"
        table_modified="Table Metadata Modified"
        library_created="Library Metadata Created"
        library_modified="Library Metadata Modified"; 
  _nobj=1; 
  _n=1; 
  call missing(library_id, table_id, _uri, _liburi, _tableuri, _packageuri, libref, engine, preassigned, hidden, 
       _modified, _created, _location,
        library_name, library_description, membertype, table_description, tablename, sastablename,
        _IsDBMSLibname
   );
  do while(_n le _nobj);
    _nobj=metadata_getnobj("omsobj:SASLibrary?@Id contains '.'",_n,_liburi);
    _rc=metadata_getattr(_liburi,"Id",library_id);
    _rc=metadata_getattr(_liburi,"Name",library_name);
    _rc=metadata_getattr(_liburi,"Desc",library_description);
    _rc=metadata_getattr(_liburi,"Libref",libref);
    _rc=metadata_getattr(_liburi,"Engine",engine);
    _rc=metadata_getattr(_liburi,"IsPreassigned",preassigned);
    _rc=metadata_getattr(_liburi,"IsHidden",hidden);
    _rc=metadata_getattr(_liburi,"IsDBMSLibname",_IsDBMSLibname);
    _rc=metadata_getattr(_liburi,"MetadataCreated",_created);
    _rc=metadata_getattr(_liburi,"MetadataUpdated",_modified);
    library_created=input(_created,anydtdtm.);
    library_modified=input(_modified,anydtdtm.);
    * Get folder object the current Library is in *;
    _rc=metadata_getnasn(_liburi,"Trees",1,_uri);
    * Get folder name the current Library is in *;
    _rc=metadata_getattr(_uri,"Name",library_location);
    _tree=1; 
    * Loop up the folder hierarchy *;
    do while (_tree>0);
      * Get the parent folder object *;
      _tree=metadata_getnasn(_uri,"ParentTree",1,_uri);
      if _tree > 0 then do; 
        * If there was a parent folder, get the name *;
        _rc=metadata_getattr(_uri,"Name",_location);
        * Construct the path *;
        library_location=catx('/',_location,library_location);
      end; 
    end; * Folder Hierachy *;
    library_location = '/'||library_location;
    
    /* Get Tables */
    if _IsDBMSLibname = "0" then do; 
      _tablerc=1;_table_count=1; 
      do while(_tablerc>0);
        call missing(table_name, table_description, membertype, sastablename, tablename,_created, _modified, table_created, table_modified, table_location);
        _tablerc=metadata_getnasn(_liburi,
                "Tables",
                _table_count,
                _tableuri);
        _arc=1; 
        if (_tablerc>0) then do; 
          _arc=metadata_getattr(_tableuri,"Id",table_id);
          _arc=metadata_getattr(_tableuri,"Name",table_name);
          _arc=metadata_getattr(_tableuri,"Desc",table_description);
          _arc=metadata_getattr(_tableuri,"MemberType",membertype);
          _arc=metadata_getattr(_tableuri,"SASTableName",sastablename);
          _arc=metadata_getattr(_tableuri,"TableName",tablename);
          _arc=metadata_getattr(_tableuri,"IsHidden",hidden);
          _arc=metadata_getattr(_tableuri,"MetadataCreated",_created);
          _arc=metadata_getattr(_tableuri,"MetadataUpdated",_modified);
          table_created=input(_created,anydtdtm.);
          table_modified=input(_modified,anydtdtm.);
  
          * Get folder object the current Table is in *;
          _arc=metadata_getnasn(_tableuri,"Trees",1,_uri);
          * Get folder name the current Table is in *;
          _arc=metadata_getattr(_uri,"Name",table_location);
          _tree=1; 
          * Loop up the folder hierarchy *;
          do while (_tree>0);
            * Get the parent folder object *;
            _tree=metadata_getnasn(_uri,"ParentTree",1,_uri);
            if _tree > 0 then do; 
              * If there was a parent folder, get the name *;
              _arc=metadata_getattr(_uri,"Name",_location);
              * Construct the path *;
              table_location=catx('/',_location,table_location);
            end; 
          end; * Folder Hierachy *;
          table_location = '/'||table_location;
  
          output; 
          _table_count=_table_count+1; 
        end; 
      end; 
    end; 
    else do; 
      _tablerc=1;_table_count=1;_packagerc=1;_package_count=1; 
      _packagerc=metadata_getnasn(_liburi,
                "UsingPackages",
                _package_count,
                _packageuri);
      if (_packagerc>0) then do while (_tablerc>0);
        call missing(table_name, table_description, membertype, sastablename, tablename,_created, _modified, table_created, table_modified, table_location);
        _tablerc=metadata_getnasn(_packageuri,
                "Tables",
                _table_count,
                _tableuri);
        _arc=1; 

        if (_tablerc>0) then do; 
          _arc=metadata_getattr(_tableuri,"Id",table_id);
          _arc=metadata_getattr(_tableuri,"Name",table_name);
          _arc=metadata_getattr(_tableuri,"Desc",table_description);
          _arc=metadata_getattr(_tableuri,"MemberType",membertype);
          _arc=metadata_getattr(_tableuri,"SASTableName",sastablename);
          _arc=metadata_getattr(_tableuri,"TableName",tablename);
          _arc=metadata_getattr(_tableuri,"IsHidden",hidden);
          _arc=metadata_getattr(_tableuri,"MetadataCreated",_created);
          _arc=metadata_getattr(_tableuri,"MetadataUpdated",_modified);
          table_created=input(_created,anydtdtm.);
          table_modified=input(_modified,anydtdtm.);
 
          * Get folder object the current Table is in *;
          _arc=metadata_getnasn(_tableuri,"Trees",1,_uri);
          * Get folder name the current Table is in *;
          _arc=metadata_getattr(_uri,"Name",table_location);
          _tree=1; 
          * Loop up the folder hierarchy *;
          do while (_tree>0);
            * Get the parent folder object *;
            _tree=metadata_getnasn(_uri,"ParentTree",1,_uri);
            if _tree > 0 then do; 
              * If there was a parent folder, get the name *;
              _arc=metadata_getattr(_uri,"Name",_location);
              * Construct the path *;
              table_location=catx('/',_location,table_location);
            end; 
          end; * Folder Hierachy *;
          table_location = '/'||table_location;    
          output; 
          _table_count=_table_count+1; 
        end; 
      end; 
    end; 
    _n=_n+1; 
  end; 
run;

/* Macro to reload one table */
%Macro ReloadTable(HDFSLIBRARY, TABLE, LASRLIB);
    /* list tables in LASR metadata */
    proc sql ;
	 select count(*) into :to_be_loaded from tablelist
	 where upcase(libref)="CO1IT01L" and upcase(table_name) = "TABEL_A_TEST";
	quit; 
	%put To Be Loaded: &to_be_loaded;
	%if &to_be_loaded = 1 %then %do;
    LIBNAME &lasrlib SASIOLA  TAG=&HDFSLIBRARY  PORT=10011 HOST="sasva1.al.ndis.be"  SIGNER="https://sas.fluvius.be:443/SASLASRAuthorization" ;
	    %if %sysfunc(exist(&lasrlib..&TABLE.)) %then %do;
	       %PUT ALREADY LOADED: &lasrlib..&TABLE;
	    %end;
	    %else %do;
		 proc lasr port=10011 data=&HDFSLIBRARY..&TABLE. add noclass
	            signer="https://sas.fluvius.be:443/SASLASRAuthorization";
	            performance host="sasva1.al.ndis.be";
	        run;
	        %PUT RELOADED: &lasrlib..&TABLE;
	    %end;
	%end;
%mend;

/* Macro to get all tables in one HDFS library */
%macro LoopLibraries(HDFSLIBRARY);
    LIBNAME &HDFSLIBRARY. SASHDAT  PATH="/&HDFSLIBRARY."  SERVER="svfl-app-p021.ndis.be"  INSTALL="/opt/sas/int/TKGrid" ;

    proc contents data=&HDFSLIBRARY.._all_ out=datasets noprint;
    run;

    proc sort nodupkey data=datasets; by libname memname; run;

    data looplibraries;
        set datasets;
        lasrlib = strip(substr(libname,1,length(libname)-1)) || "L";
		if memname = "TABEL_A_TEST";
        call execute('%ReloadTable('||strip(libname)||','||strip(memname)||','||strip(lasrlib)||');');
    run;
%mend;

data temp(keep=libref saslibname properties location);
    set libservercomp;
	if libref in ('co1it01h');
    /*if libref in(&listoflibraries);*/
    if substr(location,1,8) = '/Fluvius' then do;
       call execute('%LoopLibraries('||strip(libref)||');');
    end;
run;

1                                                          The SAS System                             13:51 Friday, January 17, 2020

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='ReloadALLTablesFromHadoop.sas';
4          %LET _CLIENTPROCESSFLOWNAME='Standalone Not In Project';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='\\ndis.be\dfsroot02\07_0060\13_Applicaties\04_Appl_OD\02_OB, R&S, BI\3. Foundation\BI & IM\SAS\2.
8        ! Documentatie\1. SAS Technisch - how to\ReloadALLTablesFromHadoop.sas';
9          %LET _SASPROGRAMFILEHOST='UW425108';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=SVG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         %macro HTML5AccessibleGraphSupported;
15             %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16         %mend;
17         FILENAME EGHTML TEMP;
18         ODS HTML5(ID=EGHTML) FILE=EGHTML
19             OPTIONS(BITMAP_MODE='INLINE')
20             %HTML5AccessibleGraphSupported
21             ENCODING='utf-8'
22             STYLE=HTMLBlue
23             NOGTITLE
24             NOGFOOTNOTE
25             GPATH=&sasworklocation
26         ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
27         
28         /*1. Get list of libraries which contain tables */
29         data hfdslibs;
30          infile "/SASDATA/DataResult/temp/list_hdfs.txt";
31          input hdfs $20.;
32         run;

NOTE: The infile "/SASDATA/DataResult/temp/list_hdfs.txt" is:
      Filename=/SASDATA/DataResult/temp/list_hdfs.txt,
      Owner Name=sassrv,Group Name=sas,
      Access Permission=-rw-r--r--,
      Last Modified=13Jan2020:15:29:22,
      File Size (bytes)=325

NOTE: 36 records were read from the infile "/SASDATA/DataResult/temp/list_hdfs.txt".
      The minimum record length was 7.
      The maximum record length was 11.
NOTE: SAS went to a new line when INPUT statement reached past the end of a line.
NOTE: The data set WORK.HFDSLIBS has 18 observations and 1 variables.
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM|         STOP| _DISARM| 2020-01-17T13:52:07,362+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| 
      _DISARM| | _DISARM| 18| _DISARM| 18780160| _DISARM| 11| _DISARM| 11| _DISARM| 136| _DISARM| 1024| _DISARM| 0.010000| _DISARM| 
      0.001794| _DISARM| 1894884727.360334| _DISARM| 1894884727.362128| _DISARM| 0.000000| _DISARM| | _ENDDISARM 
NOTE: PROCEDURE| _DISARM|         STOP| _DISARM| 2020-01-17T13:52:07,362+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | 
      _DISARM| 18780160| _DISARM| 18780160| _DISARM| 11| _DISARM| 11| _DISARM| 136| _DISARM| 1024| _DISARM| 0.010000| _DISARM| 
      0.004181| _DISARM| 1894884727.358281| _DISARM| 1894884727.362462| _DISARM| 0.000000| _DISARM| | _ENDDISARM 
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
      

2                                                          The SAS System                             13:51 Friday, January 17, 2020

33         
34         proc sql noprint;
35          select distinct "'" || strip(hdfs) || "'" into: listoflibraries separated by ','  from hfdslibs;
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM|         STOP| _DISARM| 2020-01-17T13:52:07,370+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| 
      _DISARM| | _DISARM| 18| _DISARM| 19042304| _DISARM| 11| _DISARM| 11| _DISARM| 8| _DISARM| 1032| _DISARM| 0.000000| _DISARM| 
      0.001434| _DISARM| 1894884727.368635| _DISARM| 1894884727.370069| _DISARM| 0.000000| _DISARM| | _ENDDISARM 
36         quit;
NOTE: PROCEDURE| _DISARM|         STOP| _DISARM| 2020-01-17T13:52:07,370+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | 
      _DISARM| 24027136| _DISARM| 19042304| _DISARM| 11| _DISARM| 11| _DISARM| 8| _DISARM| 1032| _DISARM| 0.000000| _DISARM| 
      0.002615| _DISARM| 1894884727.367736| _DISARM| 1894884727.370351| _DISARM| 0.000000| _DISARM| | _ENDDISARM 
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

37         %put &listoflibraries;
'bf1bu01h','bf2bg01h','bf4bu01h','co1it01h','ed1bg04h','ed1it01h','fi2bu01h','hdfs_am','hdfs_ely','ic1bu01h','mw2bg03h','mw2it02h','
mw2it07h','nb1it01h','nb4it01h','ob1bu01h','ob1it01h','vapublicint'
38         
39         /*2. Assign */
40         
50         libname formats base "/SASDATA/DataResult/formats/";
NOTE: Libref FORMATS was successfully assigned as follows: 
      Engine:        BASE 
      Physical Name: /SASDATA/DataResult/formats
51         options FMTSEARCH = (formats);
52         
53         /* 3. Get metadata hadoop and LASR libraries */
54         data LibServerComp LibServerCompLASR (drop=LasrPortC);
55         		length uri    uriTrees    uriTree    uriUP    uriProp    uriConn    uriDom    $256
56         			Server    $8
57         			Metaserver    $50
58         			Metaport    VAauthport    RowNumber    4.
59         			Metauser    $20
60         			Metapass    $56
61         			Metarepository    $15
62         			Metaprotocol    $6
63         			VAlasrport    5.
64         			LasrPortC    $5
65         			TKGrid    $30
66         			SASLibName    Engine    Libref    ParentLocation    Properties    Tag    $60
67         			IsPreassigned    $1
68         			UsingPackages    Location    $512;
69         	nobj=1;
70         	n=1;
71         	do while(nobj >= 0);
72         		nobj=metadata_getnobj("omsobj:SASLibrary?@Id contains '.'",n,uri);
73         		if (nobj < 0) then leave;
74         		rc=metadata_getattr(uri,"Name",SASLibName);
3                                                          The SAS System                             13:51 Friday, January 17, 2020

75         		rc=metadata_getattr(uri,"Engine",Engine);
76         		rc=metadata_getattr(uri,"IsPreassigned",IsPreassigned);
77         		rc=metadata_getattr(uri,"Libref",Libref);
78         		RowNumber=n;
79         		
80         		if (Engine eq "SASIOLA" OR Engine eq "SASHDAT" OR Engine eq "BASE" OR Engine eq "SQLSVR") then do;
81         			uriTrees=' ';
82         			Location=' ';
83         			uriUP=' ';
84         			UsingPackages=' ';
85         			uriProp=' ';
86         			uriDom=' ';
87         			Properties=' ';
88         			Tag=' ';
89         			uriConn=' ';
90         			LasrPortC=' ';
91         			
92         			rc=metadata_getnasn(uri,"Trees",1,uriTrees);
93         			rc=metadata_getattr(uriTrees,"Name",Location);
94         			Location=catt(Location,"/");
95         			
96         			do until (rc < 0);
97         				uriTree=' ';
98         				ParentLocation=' ';
99         				rc=metadata_getnasn(uriTrees,"ParentTree",1,uriTree);
100        				rc=metadata_getattr(uriTree,"Name",ParentLocation);
101        				
102        				uriTrees=uriTree;
103        				
104        				Location=catt(trim(ParentLocation), '/', Location);
105        			end;
106        			
107        			rc=metadata_getnasn(uri,"UsingPackages",1,uriUP);
108        			if (Engine eq "SASIOLA" OR Engine eq "SASHDAT" OR Engine eq "SQLSVR") then
109        				rc=metadata_getattr(uriUP,"SchemaName",UsingPackages);
110        			
111        			if Engine eq "SASIOLA" then
112        				do;
113        					rc=metadata_getnasn(uri,"Properties",1,uriProp);
114        					rc=metadata_getattr(uriProp,"DefaultValue",Properties);
115        					if Properties ne LowCase(Properties) then
116        						put "WARNING: Libray " SASLibName " has a Server Tag " Properties " that should be in lowercase.";
117        					Tag=LowCase(Properties);
118        					Properties=catt('/',translate(LowCase(Properties),'/','.'));
119        					rc=metadata_getnasn(uri,"LibraryConnection",1,uriConn);
120        					rc=metadata_getattr(uriConn,"Port",LasrPortC);
121        					VAlasrport=put(strip(LasrPortC),5.);
122        					output LibServerCompLASR;
123        					end;
124        			if Engine eq "SASHDAT" then
125        				do;
126        					rc=metadata_getnasn(uri,"Properties",1,uriProp);
127        					rc=metadata_getattr(uriProp,"DefaultValue",Properties);
128        					if Properties ne LowCase(Properties) then
129        						put "WARNING: Libray " SASLibName " has a HDFS Path " Properties " that should be in lowercase.";
130        				output LibServerComp;
131        				end;		
132        		
4                                                          The SAS System                             13:51 Friday, January 17, 2020

133        		end;
134        		n=n+1;
135        	end;
136        run;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
      121:17   
NOTE: Variable uri is uninitialized.
NOTE: Variable Server is uninitialized.
NOTE: Variable Metaserver is uninitialized.
NOTE: Variable Metaport is uninitialized.
NOTE: Variable VAauthport is uninitialized.
NOTE: Variable Metauser is uninitialized.
NOTE: Variable Metapass is uninitialized.
NOTE: Variable Metarepository is uninitialized.
NOTE: Variable Metaprotocol is uninitialized.
NOTE: Variable TKGrid is uninitialized.
NOTE: Variable SASLibName is uninitialized.
NOTE: Variable Engine is uninitialized.
NOTE: Variable Libref is uninitialized.
NOTE: Variable IsPreassigned is uninitialized.
WARNING: Libray LASR_BUS_CRM  has a Server Tag LSR_B_CRM  that should be in lowercase.
WARNING: Libray Environment Manager Data Mart LASR  has a Server Tag EVDM  that should be in lowercase.
WARNING: Libray Visual Analytics Public LASR  has a Server Tag VAPUBLICINT  that should be in lowercase.
WARNING: Libray Visual Analytics LASR  has a Server Tag HPSINT  that should be in lowercase.
NOTE: The data set WORK.LIBSERVERCOMP has 208 observations and 31 variables.
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM|         STOP| _DISARM| 2020-01-17T13:52:12,252+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| 
      _DISARM| | _DISARM| 208| _DISARM| 20619264| _DISARM| 11| _DISARM| 11| _DISARM| 3088| _DISARM| 4120| _DISARM| 2.710000| 
      _DISARM| 4.876860| _DISARM| 1894884727.375906| _DISARM| 1894884732.252766| _DISARM| 2.110000| _DISARM| | _ENDDISARM 
NOTE: The data set WORK.LIBSERVERCOMPLASR has 208 observations and 30 variables.
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM|         STOP| _DISARM| 2020-01-17T13:52:12,253+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| 
      _DISARM| | _DISARM| 208| _DISARM| 20353024| _DISARM| 11| _DISARM| 11| _DISARM| 3080| _DISARM| 4120| _DISARM| 2.700000| 
      _DISARM| 4.876987| _DISARM| 1894884727.376447| _DISARM| 1894884732.253434| _DISARM| 2.100000| _DISARM| | _ENDDISARM 
NOTE: PROCEDURE| _DISARM|         STOP| _DISARM| 2020-01-17T13:52:12,253+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | 
      _DISARM| 64401408| _DISARM| 20353024| _DISARM| 11| _DISARM| 11| _DISARM| 3088| _DISARM| 4120| _DISARM| 2.710000| _DISARM| 
      4.881445| _DISARM| 1894884727.372251| _DISARM| 1894884732.253696| _DISARM| 2.110000| _DISARM| | _ENDDISARM 
NOTE: DATA statement used (Total process time):
      real time           4.88 seconds
      cpu time            2.71 seconds
      

137        
138        /* 4. get metadata tables */
139        data work.tablelist;
140          length libref $ 8 table_name $ 32 library_id table_id $17 _uri _liburi _tableuri _packageuri library_name
140      ! library_description engine preassigned hidden
141            membertype table_description tablename sastablename _modified _created library_location table_location _location $256
141      ! ;
142          length library_created library_modified table_created table_modified 8;
143          length _IsDBMSLibname $ 1;
144          format library_created library_modified table_created table_modified datetime.;
145          label library_id="Library ID"
146                table_id="Table ID"
147                library_name="Library Object Name"
148                library_description="Library Description"
149                library_location="Library Folder Location"
150                libref="Library Reference"
151                engine="Library Engine"
5                                                          The SAS System                             13:51 Friday, January 17, 2020

152                preassigned="Pre-Assigned Flag"
153                hidden="Hidden Flag"
154                table_name="Table Object Name"
155                table_description="Table Description"
156                tablename="Table Name"
157                membertype="Member Type"
158                sastablename="SAS Table Name"
159                table_location="Table Folder Location"
160                table_created="Table Metadata Created"
161                table_modified="Table Metadata Modified"
162                library_created="Library Metadata Created"
163                library_modified="Library Metadata Modified";
164          _nobj=1;
165          _n=1;
166          call missing(library_id, table_id, _uri, _liburi, _tableuri, _packageuri, libref, engine, preassigned, hidden,
167               _modified, _created, _location,
168                library_name, library_description, membertype, table_description, tablename, sastablename,
169                _IsDBMSLibname
170           );
171          do while(_n le _nobj);
172            _nobj=metadata_getnobj("omsobj:SASLibrary?@Id contains '.'",_n,_liburi);
173            _rc=metadata_getattr(_liburi,"Id",library_id);
174            _rc=metadata_getattr(_liburi,"Name",library_name);
175            _rc=metadata_getattr(_liburi,"Desc",library_description);
176            _rc=metadata_getattr(_liburi,"Libref",libref);
177            _rc=metadata_getattr(_liburi,"Engine",engine);
178            _rc=metadata_getattr(_liburi,"IsPreassigned",preassigned);
179            _rc=metadata_getattr(_liburi,"IsHidden",hidden);
180            _rc=metadata_getattr(_liburi,"IsDBMSLibname",_IsDBMSLibname);
181            _rc=metadata_getattr(_liburi,"MetadataCreated",_created);
182            _rc=metadata_getattr(_liburi,"MetadataUpdated",_modified);
183            library_created=input(_created,anydtdtm.);
184            library_modified=input(_modified,anydtdtm.);
185            * Get folder object the current Library is in *;
186            _rc=metadata_getnasn(_liburi,"Trees",1,_uri);
187            * Get folder name the current Library is in *;
188            _rc=metadata_getattr(_uri,"Name",library_location);
189            _tree=1;
190            * Loop up the folder hierarchy *;
191            do while (_tree>0);
192              * Get the parent folder object *;
193              _tree=metadata_getnasn(_uri,"ParentTree",1,_uri);
194              if _tree > 0 then do;
195                * If there was a parent folder, get the name *;
196                _rc=metadata_getattr(_uri,"Name",_location);
197                * Construct the path *;
198                library_location=catx('/',_location,library_location);
199              end;
200            end; * Folder Hierachy *;
201            library_location = '/'||library_location;
202        
203            /* Get Tables */
204            if _IsDBMSLibname = "0" then do;
205              _tablerc=1;_table_count=1;
206              do while(_tablerc>0);
207                call missing(table_name, table_description, membertype, sastablename, tablename,_created, _modified,
207      ! table_created, table_modified, table_location);
208                _tablerc=metadata_getnasn(_liburi,
6                                                          The SAS System                             13:51 Friday, January 17, 2020

209                        "Tables",
210                        _table_count,
211                        _tableuri);
212                _arc=1;
213                if (_tablerc>0) then do;
214                  _arc=metadata_getattr(_tableuri,"Id",table_id);
215                  _arc=metadata_getattr(_tableuri,"Name",table_name);
216                  _arc=metadata_getattr(_tableuri,"Desc",table_description);
217                  _arc=metadata_getattr(_tableuri,"MemberType",membertype);
218                  _arc=metadata_getattr(_tableuri,"SASTableName",sastablename);
219                  _arc=metadata_getattr(_tableuri,"TableName",tablename);
220                  _arc=metadata_getattr(_tableuri,"IsHidden",hidden);
221                  _arc=metadata_getattr(_tableuri,"MetadataCreated",_created);
222                  _arc=metadata_getattr(_tableuri,"MetadataUpdated",_modified);
223                  table_created=input(_created,anydtdtm.);
224                  table_modified=input(_modified,anydtdtm.);
225        
226                  * Get folder object the current Table is in *;
227                  _arc=metadata_getnasn(_tableuri,"Trees",1,_uri);
228                  * Get folder name the current Table is in *;
229                  _arc=metadata_getattr(_uri,"Name",table_location);
230                  _tree=1;
231                  * Loop up the folder hierarchy *;
232                  do while (_tree>0);
233                    * Get the parent folder object *;
234                    _tree=metadata_getnasn(_uri,"ParentTree",1,_uri);
235                    if _tree > 0 then do;
236                      * If there was a parent folder, get the name *;
237                      _arc=metadata_getattr(_uri,"Name",_location);
238                      * Construct the path *;
239                      table_location=catx('/',_location,table_location);
240                    end;
241                  end; * Folder Hierachy *;
242                  table_location = '/'||table_location;
243        
244                  output;
245                  _table_count=_table_count+1;
246                end;
247              end;
248            end;
249            else do;
250              _tablerc=1;_table_count=1;_packagerc=1;_package_count=1;
251              _packagerc=metadata_getnasn(_liburi,
252                        "UsingPackages",
253                        _package_count,
254                        _packageuri);
255              if (_packagerc>0) then do while (_tablerc>0);
256                call missing(table_name, table_description, membertype, sastablename, tablename,_created, _modified,
256      ! table_created, table_modified, table_location);
257                _tablerc=metadata_getnasn(_packageuri,
258                        "Tables",
259                        _table_count,
260                        _tableuri);
261                _arc=1;
262        
263                if (_tablerc>0) then do;
264                  _arc=metadata_getattr(_tableuri,"Id",table_id);
265                  _arc=metadata_getattr(_tableuri,"Name",table_name);
7                                                          The SAS System                             13:51 Friday, January 17, 2020

266                  _arc=metadata_getattr(_tableuri,"Desc",table_description);
267                  _arc=metadata_getattr(_tableuri,"MemberType",membertype);
268                  _arc=metadata_getattr(_tableuri,"SASTableName",sastablename);
269                  _arc=metadata_getattr(_tableuri,"TableName",tablename);
270                  _arc=metadata_getattr(_tableuri,"IsHidden",hidden);
271                  _arc=metadata_getattr(_tableuri,"MetadataCreated",_created);
272                  _arc=metadata_getattr(_tableuri,"MetadataUpdated",_modified);
273                  table_created=input(_created,anydtdtm.);
274                  table_modified=input(_modified,anydtdtm.);
275        
276                  * Get folder object the current Table is in *;
277                  _arc=metadata_getnasn(_tableuri,"Trees",1,_uri);
278                  * Get folder name the current Table is in *;
279                  _arc=metadata_getattr(_uri,"Name",table_location);
280                  _tree=1;
281                  * Loop up the folder hierarchy *;
282                  do while (_tree>0);
283                    * Get the parent folder object *;
284                    _tree=metadata_getnasn(_uri,"ParentTree",1,_uri);
285                    if _tree > 0 then do;
286                      * If there was a parent folder, get the name *;
287                      _arc=metadata_getattr(_uri,"Name",_location);
288                      * Construct the path *;
289                      table_location=catx('/',_location,table_location);
290                    end;
291                  end; * Folder Hierachy *;
292                  table_location = '/'||table_location;
293                  output;
294                  _table_count=_table_count+1;
295                end;
296              end;
297            end;
298            _n=_n+1;
299          end;
300        run;

NOTE: The data set WORK.TABLELIST has 440 observations and 36 variables.
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM|         STOP| _DISARM| 2020-01-17T13:52:16,992+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| 
      _DISARM| | _DISARM| 440| _DISARM| 20353024| _DISARM| 11| _DISARM| 11| _DISARM| 4360| _DISARM| 8480| _DISARM| 2.590000| 
      _DISARM| 4.731516| _DISARM| 1894884732.261147| _DISARM| 1894884736.992663| _DISARM| 2.060000| _DISARM| | _ENDDISARM 
NOTE: PROCEDURE| _DISARM|         STOP| _DISARM| 2020-01-17T13:52:16,993+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | 
      _DISARM| 70688768| _DISARM| 20353024| _DISARM| 11| _DISARM| 11| _DISARM| 4360| _DISARM| 8480| _DISARM| 2.590000| _DISARM| 
      4.737216| _DISARM| 1894884732.255845| _DISARM| 1894884736.993061| _DISARM| 2.060000| _DISARM| | _ENDDISARM 
NOTE: DATA statement used (Total process time):
      real time           4.73 seconds
      cpu time            2.59 seconds
      

301        
302        %global to_be_loaded;
303        /* Macro to reload one table */
304        %Macro ReloadTable(HDFSLIBRARY, TABLE, LASRLIB);
305        
306        	%put &hdfslibrary;
307        	%put &table;
308        	%put &lasrlib;
309        	/*%let to_be_loaded = 0;*/
310            /* list tables in LASR metadata */
8                                                          The SAS System                             13:51 Friday, January 17, 2020

311            /*proc sql ;
312        	 select count(*) into :to_be_loaded from tablelist
313        	 where upcase(libref)="CO1IT01L" and upcase(table_name) = "TABEL_A_TEST";
314        	quit; */
315        
316        	proc sql ;
317        	 create table filip as select * from tablelist
318        	 where upcase(libref)="CO1IT01L" and upcase(table_name) = "TABEL_A_TEST";
319        	quit;
320        
321        	proc sql;
322        	 select count(*) into :to_be_loaded from filip;
323        	quit;
324        	%put To Be Loaded: &to_be_loaded;
325        	%if &to_be_loaded = 1 %then %do;
326            LIBNAME &lasrlib SASIOLA  TAG=&HDFSLIBRARY  PORT=10011 HOST="sasva1.al.ndis.be"
326      ! SIGNER="https://sas.fluvius.be:443/SASLASRAuthorization" ;
327        	    %if %sysfunc(exist(&lasrlib..&TABLE.)) %then %do;
328        	       %PUT ALREADY LOADED: &lasrlib..&TABLE;
329        	    %end;
330        	    %else %do;
331        		 proc lasr port=10011 data=&HDFSLIBRARY..&TABLE. add noclass
332        	            signer="https://sas.fluvius.be:443/SASLASRAuthorization";
333        	            performance host="sasva1.al.ndis.be";
334        	        run;
335        	        %PUT RELOADED: &lasrlib..&TABLE;
336        	    %end;
337        	%end;
338        %mend;
339        
340        /* Macro to get all tables in one HDFS library */
341        %macro LoopLibraries(HDFSLIBRARY);
342            LIBNAME &HDFSLIBRARY. SASHDAT  PATH="/&HDFSLIBRARY."  SERVER="svfl-app-p021.ndis.be"  INSTALL="/opt/sas/int/TKGrid" ;
343        
344            proc contents data=&HDFSLIBRARY.._all_ out=datasets noprint;
345            run;
346        
347            proc sort nodupkey data=datasets; by libname memname; run;
348        
349            data looplibraries;
350                set datasets;
351                lasrlib = strip(substr(libname,1,length(libname)-1)) || "L";
352        		if memname = "TABEL_A_TEST";
353                call execute('%ReloadTable('||strip(libname)||','||strip(memname)||','||strip(lasrlib)||');');
354            run;
355        %mend;
356        
357        data temp(keep=libref saslibname properties location);
358            set libservercomp;
359        	if libref in ('co1it01h');
360            /*if libref in(&listoflibraries);*/
361            if substr(location,1,8) = '/Fluvius' then do;
362               call execute('%LoopLibraries('||strip(libref)||');');
363            end;
364        run;

NOTE: There were 208 observations read from the data set WORK.LIBSERVERCOMP.
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM|         STOP| _DISARM| 2020-01-17T13:52:17,000+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| 
9                                                          The SAS System                             13:51 Friday, January 17, 2020

      _DISARM| | _DISARM| 208| _DISARM| 20353024| _DISARM| 11| _DISARM| 11| _DISARM| 8| _DISARM| 8488| _DISARM| 0.010000| _DISARM| 
      0.002492| _DISARM| 1894884736.998014| _DISARM| 1894884737.000506| _DISARM| 0.000000| _DISARM| | _ENDDISARM 
NOTE: The data set WORK.TEMP has 1 observations and 4 variables.
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM|         STOP| _DISARM| 2020-01-17T13:52:17,001+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| 
      _DISARM| | _DISARM| 1| _DISARM| 20353024| _DISARM| 11| _DISARM| 11| _DISARM| 144| _DISARM| 8624| _DISARM| 0.010000| _DISARM| 
      0.001646| _DISARM| 1894884736.999429| _DISARM| 1894884737.001075| _DISARM| 0.000000| _DISARM| | _ENDDISARM 
NOTE: PROCEDURE| _DISARM|         STOP| _DISARM| 2020-01-17T13:52:17,001+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | 
      _DISARM| 70688768| _DISARM| 20353024| _DISARM| 11| _DISARM| 11| _DISARM| 144| _DISARM| 8624| _DISARM| 0.010000| _DISARM| 
      0.004804| _DISARM| 1894884736.996501| _DISARM| 1894884737.001305| _DISARM| 0.000000| _DISARM| | _ENDDISARM 
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
      

NOTE: CALL EXECUTE generated line.
1         + LIBNAME co1it01h SASHDAT  PATH="/co1it01h"  SERVER="svfl-app-p021.ndis.be"  INSTALL="/opt/sas/int/TKGrid" ;
NOTE: Libref CO1IT01H was successfully assigned as follows: 
      Engine:        SASHDAT 
      Physical Name: Directory '/co1it01h' of HDFS cluster on host 'svfl-app-p021.ndis.be'
1         +                                                                                                                  proc 
contents data=co1it01h._all_ out=datasets noprint;     run;      proc sort nodupkey data=datasets; by libname memname; run;      
data

NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM|         STOP| _DISARM| 2020-01-17T13:52:26,244+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| 
      _DISARM| | _DISARM| -1| _DISARM| 20353024| _DISARM| 14| _DISARM| 14| _DISARM| 288| _DISARM| 8928| _DISARM| 0.000000| _DISARM| 
      0.000995| _DISARM| 1894884746.243680| _DISARM| 1894884746.244675| _DISARM| 0.000000| _DISARM| | _ENDDISARM 
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM|         STOP| _DISARM| 2020-01-17T13:52:31,046+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| 
      _DISARM| | _DISARM| -1| _DISARM| 20353024| _DISARM| 14| _DISARM| 14| _DISARM| 8| _DISARM| 8936| _DISARM| 0.000000| _DISARM| 
      0.000417| _DISARM| 1894884751.046379| _DISARM| 1894884751.046796| _DISARM| 0.000000| _DISARM| | _ENDDISARM 
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM|         STOP| _DISARM| 2020-01-17T13:52:35,952+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| 
      _DISARM| | _DISARM| -1| _DISARM| 20353024| _DISARM| 14| _DISARM| 14| _DISARM| 144| _DISARM| 9080| _DISARM| 0.000000| _DISARM| 
      0.000540| _DISARM| 1894884755.952152| _DISARM| 1894884755.952692| _DISARM| 0.000000| _DISARM| | _ENDDISARM 
NOTE: The data set WORK.DATASETS has 274 observations and 41 variables.
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM|         STOP| _DISARM| 2020-01-17T13:52:35,953+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| 
      _DISARM| | _DISARM| 274| _DISARM| 20353024| _DISARM| 14| _DISARM| 14| _DISARM| 600| _DISARM| 9224| _DISARM| 0.030000| 
      _DISARM| 16.251850| _DISARM| 1894884739.701571| _DISARM| 1894884755.953421| _DISARM| 0.030000| _DISARM| | _ENDDISARM 
NOTE: PROCEDURE CONTENTS used (Total process time):
      real time           16.25 seconds
      cpu time            0.03 seconds
      
NOTE: PROCEDURE| _DISARM|         STOP| _DISARM| 2020-01-17T13:52:35,953+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | 
      _DISARM| 70688768| _DISARM| 20353024| _DISARM| 14| _DISARM| 14| _DISARM| 600| _DISARM| 9224| _DISARM| 0.030000| _DISARM| 
      16.253772| _DISARM| 1894884739.700117| _DISARM| 1894884755.953889| _DISARM| 0.030000| _DISARM| | _ENDDISARM 


NOTE: There were 274 observations read from the data set WORK.DATASETS.
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM|         STOP| _DISARM| 2020-01-17T13:52:35,958+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| 
      _DISARM| | _DISARM| 274| _DISARM| 20885504| _DISARM| 14| _DISARM| 14| _DISARM| 16| _DISARM| 9248| _DISARM| 0.000000| _DISARM| 
      0.002082| _DISARM| 1894884755.956280| _DISARM| 1894884755.958362| _DISARM| 0.000000| _DISARM| | _ENDDISARM 
NOTE: 271 observations with duplicate key values were deleted.
NOTE: The data set WORK.DATASETS has 3 observations and 41 variables.
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM|         STOP| _DISARM| 2020-01-17T13:52:35,958+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| 
      _DISARM| | _DISARM| 3| _DISARM| 20353024| _DISARM| 14| _DISARM| 14| _DISARM| 160| _DISARM| 9392| _DISARM| 0.000000| _DISARM| 
      0.002106| _DISARM| 1894884755.956899| _DISARM| 1894884755.959005| _DISARM| 0.000000| _DISARM| | _ENDDISARM 
NOTE: PROCEDURE| _DISARM|         STOP| _DISARM| 2020-01-17T13:52:35,959+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | 
      _DISARM| 70688768| _DISARM| 20353024| _DISARM| 14| _DISARM| 14| _DISARM| 160| _DISARM| 9392| _DISARM| 0.000000| _DISARM| 
      0.003680| _DISARM| 1894884755.955580| _DISARM| 1894884755.959260| _DISARM| 0.000000| _DISARM| | _ENDDISARM 
NOTE: PROCEDURE SORT used (Total process time):
10                                                         The SAS System                             13:51 Friday, January 17, 2020

      real time           0.00 seconds
      cpu time            0.00 seconds
      

2         + looplibraries;         set datasets;         lasrlib = strip(substr(libname,1,length(libname)-1)) || "L";   if memname 
= "TABEL_A_TEST";         call execute('%ReloadTable('||strip(libname)||','||strip(memname)||','||strip(lasrlib)||');');     run;

2         +
;          
CO1IT01H
TABEL_A_TEST
CO1IT01L
To Be Loaded:
NOTE: There were 3 observations read from the data set WORK.DATASETS.
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM|         STOP| _DISARM| 2020-01-17T13:52:35,962+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| 
      _DISARM| | _DISARM| 3| _DISARM| 20353024| _DISARM| 14| _DISARM| 14| _DISARM| 8| _DISARM| 9400| _DISARM| 0.000000| _DISARM| 
      0.001827| _DISARM| 1894884755.960620| _DISARM| 1894884755.962447| _DISARM| 0.000000| _DISARM| | _ENDDISARM 
NOTE: The data set WORK.LOOPLIBRARIES has 1 observations and 42 variables.
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM|         STOP| _DISARM| 2020-01-17T13:52:35,962+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| 
      _DISARM| | _DISARM| 1| _DISARM| 20353024| _DISARM| 14| _DISARM| 14| _DISARM| 152| _DISARM| 9544| _DISARM| 0.000000| _DISARM| 
      0.001357| _DISARM| 1894884755.961572| _DISARM| 1894884755.962929| _DISARM| 0.000000| _DISARM| | _ENDDISARM 
NOTE: PROCEDURE| _DISARM|         STOP| _DISARM| 2020-01-17T13:52:35,963+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | 
      _DISARM| 70688768| _DISARM| 20353024| _DISARM| 14| _DISARM| 14| _DISARM| 152| _DISARM| 9544| _DISARM| 0.000000| _DISARM| 
      0.003214| _DISARM| 1894884755.959980| _DISARM| 1894884755.963194| _DISARM| 0.000000| _DISARM| | _ENDDISARM 
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

NOTE: CALL EXECUTE generated line.
1         + proc sql ;
1         +              create table filip as select * from tablelist   where upcase(libref)="CO1IT01L" and upcase(table_name) = 
"TABEL_A_TEST";
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM|         STOP| _DISARM| 2020-01-17T13:52:35,967+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| 
      _DISARM| | _DISARM| 440| _DISARM| 20619264| _DISARM| 14| _DISARM| 14| _DISARM| 8| _DISARM| 9552| _DISARM| 0.000000| _DISARM| 
      0.002359| _DISARM| 1894884755.964893| _DISARM| 1894884755.967252| _DISARM| 0.000000| _DISARM| | _ENDDISARM 
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM|         STOP| _DISARM| 2020-01-17T13:52:35,967+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| 
      _DISARM| | _DISARM| 1| _DISARM| 20353024| _DISARM| 14| _DISARM| 14| _DISARM| 264| _DISARM| 9808| _DISARM| 0.000000| _DISARM| 
      0.002220| _DISARM| 1894884755.965723| _DISARM| 1894884755.967943| _DISARM| 0.000000| _DISARM| | _ENDDISARM 
NOTE: Table WORK.FILIP created, with 1 rows and 36 columns.

1         +
  quit;    
NOTE: PROCEDURE| _DISARM|         STOP| _DISARM| 2020-01-17T13:52:35,968+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | 
      _DISARM| 70688768| _DISARM| 20353024| _DISARM| 14| _DISARM| 14| _DISARM| 264| _DISARM| 9808| _DISARM| 0.000000| _DISARM| 
      0.004512| _DISARM| 1894884755.963801| _DISARM| 1894884755.968313| _DISARM| 0.000000| _DISARM| | _ENDDISARM 
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

1         +
proc sql;  
1         +
   select count(*) into :to_be_loaded from filip;
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM|         STOP| _DISARM| 2020-01-17T13:52:35,978+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| 
      _DISARM| | _DISARM| 1| _DISARM| 21139456| _DISARM| 14| _DISARM| 14| _DISARM| 0| _DISARM| 9816| _DISARM| 0.010000| _DISARM| 
      0.008970| _DISARM| 1894884755.969762| _DISARM| 1894884755.978732| _DISARM| 0.000000| _DISARM| | _ENDDISARM 
11                                                         The SAS System                             13:51 Friday, January 17, 2020

1         +
  quit;
NOTE: PROCEDURE| _DISARM|         STOP| _DISARM| 2020-01-17T13:52:35,979+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | 
      _DISARM| 70688768| _DISARM| 21139456| _DISARM| 14| _DISARM| 14| _DISARM| 8| _DISARM| 9816| _DISARM| 0.020000| _DISARM| 
      0.010285| _DISARM| 1894884755.968957| _DISARM| 1894884755.979242| _DISARM| 0.010000| _DISARM| | _ENDDISARM 
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.02 seconds
      

1         +
;          
365        
366        
367        
368        %LET _CLIENTTASKLABEL=;
369        %LET _CLIENTPROCESSFLOWNAME=;
370        %LET _CLIENTPROJECTPATH=;
371        %LET _CLIENTPROJECTPATHHOST=;
372        %LET _CLIENTPROJECTNAME=;
373        %LET _SASPROGRAMFILE=;
374        %LET _SASPROGRAMFILEHOST=;
375        
376        ;*';*";*/;quit;run;
377        ODS _ALL_ CLOSE;
378        
379        
380        QUIT; RUN;
381

Filipvdr · Posted 01-17-2020 08:33 AM

Adding the %nrstr solved it apparently..

Tom · Posted 01-17-2020 08:49 AM

There is a timing issue if you use CALL EXECUTE() to invoke a macro.

The first issue is that if you use double quotes in the CALL EXECUTE() statement

call execute("%mymacro()");

the macro will be evaluated before the data step runs and the generated code will be included as constant text. Using single quotes will prevent the macro processor from evaluating the macro expressions.

call execute('%mymacro()');

But the second issue is a little harder to see. SAS will evaluate the macro expression while CALL EXECUTE() is passing the string to the stack to run after the current data step finishes. For simple macros this does not cause a problem (other than making a very ugly SAS log). But for a macro that changes the values of macro variables using SAS code (call symputx() calls or INTO clause in PROC SQL) and then tries to use those changed values in macro logic you have a problem. The decision about what code to generate is made BEFORE the code that sets the macro variable has had a chance to run.

That is why %NRSTR() helps. It prevents the macro from running while CALL EXECUTE() is running. Instead the call to the macro is pushed onto the stack. And the SAS log also looks nicer since it will just show the macro call instead of all of the statements the macro generates.

call execute('%nrstr(%mymacro)()');

Quentin · Posted 01-17-2020 09:14 AM

Hi,

I suspect this is a CALL EXECUTE timing problem.

When CALL EXECUTE executes code that uses data to generate macro variables, the timing is tricky. Your macro:

%macro LoopLibraries(HDFSLIBRARY);
    LIBNAME &HDFSLIBRARY. SASHDAT  PATH="/&HDFSLIBRARY."  SERVER="svfl-app-p021.ndis.be"  INSTALL="/opt/sas/int/TKGrid" ;

    proc contents data=&HDFSLIBRARY.._all_ out=datasets noprint;
    run;

    proc sort nodupkey data=datasets; by libname memname; run;

    data looplibraries;
        set datasets;
        lasrlib = strip(substr(libname,1,length(libname)-1)) || "L";
		if memname = "TABEL_A_TEST";
        call execute('%ReloadTable('||strip(libname)||','||strip(memname)||','||strip(lasrlib)||');');
    run;
%mend;

Uses CALL EXECUTE to invoke %RELOADTABLE. It will immediately execute %RELOADTABLE and generate a bunch of SAS code and write it to the input stack. But all of that code is written to the input stack before

proc sql ;
	 select count(*) into :to_be_loaded from tablelist

has executed, so &to_be_loaded is not what you expect.

To avoid this problem, you can add %NRSTR() to your call execute:

call execute('%nrstr(%ReloadTable('||strip(libname)||','||strip(memname)||','||strip(lasrlib)||'))');

This will allow call execute to place the macro call on the input stack, rather than execute the macro.

It allows the timing to work out, and also makes the log cleaner, because you see the macro call generated by call execute.

For more explanation of this CALL EXECUTE timing issue, see e.g. https://blogs.sas.com/content/sgf/2017/08/02/call-execute-for-sas-data-driven-programming/

BASUG is hosting free webinars Next up: Don Henderson presenting on using hash functions (not hash tables!) to segment data on June 12. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.

Ready to join fellow brilliant minds for the SAS Hackathon?

Classroom Training Available!