Using macro variable for partial dataset name does not work with background submission

[ojaro]

Hi experts,

I am using the following code in SASStudioV (Viya 3.5) to read list of datasets within my library and then dynamically loop through them and append the data to the master table.

proc sql /*noprint*/;
	  create table mcdtemp.enumtables as
	  select memname
	  from dictionary.tables
	  where libname = 'JSONDATA'
	  and memname not like ('ROOT')
	  and memname not like ('ALLDATA')
	  and memname not like ('DOCS');
	quit;
	
	data mcdtemp.enumtables;
		set mcdtemp.enumtables;
		enum_type=strip(substr(memname,6));
		drop memname;
	run;

	proc sql /*noprint*/;   
		select enum_type      
		into :mc_enum_ts
		separated by ' '
		from mcdtemp.enumtables;   
	quit;

	%let enumCount = %sysfunc(countw(&mc_enum_ts.,' '));
	%do i=1 %to &enumCount;
	
		%let mc_enumt=%scan(&mc_enum_ts.,&i.,' ');
		
		data mcdtemp.enumdata_temp;
		set jsondata.docs_&mc_enumt.;
		enum_type="&mc_enumt.";
		array j &mc_enumt.1-&mc_enumt.100;
			do over j;
			value=compress(j);
			output;
		end;
	
		keep ordinal_docs enum_type value;
	run;
	
	data mcdtemp.enumdata_temp;
		set mcdtemp.enumdata_temp;
		
		if missing(value) or trim(value)='' then delete;
	
		doc_id=ordinal_docs + put(&mc_page_c.,3.) * 10000;
		drop ordinal_docs;
	run;
	
	proc append base=mcdtemp.&mc_doc_enum_table data=mcdtemp.enumdata_temp force;
	
	%end;

The code seems to be working fine when I am testing it by running it in SASStudioV. However, when I try to do a background submission, to see how the code would run as a scheduled job, I get weird error referencing to work library (please see the error below) even if I am not using it anywhere in my code:

source: data mcdtemp.enumdata_temp;   set
source: 5    + jsondata.docs_&mc_enum_ts.;   enum_type="&mc_enum_ts.";   array j &mc_enum_ts.1-&mc_enum_ts.100;    do over j;    
source: value=compress(j);    output;   end;     keep ordinal_docs enum_type value;  run;    data mcdtemp.enumdata_temp;   set 
source: mcdtemp.enumdata_temp
error: ERROR: File WORK.DOMAIN_NAME.DATA does not exist.
error: ERROR: File WORK.EMAIL.DATA does not exist.
error: ERROR: File WORK.HASHTAG.DATA does not exist.
error: ERROR: File WORK.HASHTAG_LC.DATA does not exist.
error: ERROR: File WORK.INFORMATION_CONTEXT.DATA does not exist.
error: ERROR: File WORK.KEYWORDS.DATA does not exist.
error: ERROR: File WORK.MEDIA.DATA does not exist.
error: ERROR: File WORK.META_SPKID.DATA does not exist.
error: ERROR: File WORK.PERSON_DETECTED.DATA does not exist.
error: ERROR: File WORK.RECIPIENT.DATA does not exist.
error: ERROR: File WORK.URL.DATA does not exist.
error: ERROR: Alphabetic prefixes for enumerated variables (URL1-CHILDREN_UID) are different.

Also note that the error message is stating that I am using macro variable mc_enum_ts in order to generate the dataset name (jsondata.docs_&mc_enum_ts.), but as you can see from my code above, I am using macro variable mc_enumt for this.

Any suggestions what might be going wrong here?

Best regards,

Olli

[Tom]

Those errors make it look like the macro variable has spaces in it.

Do you have different settings for the VALIDVARNAMES option in the two environments?

[ojaro]

Hi @Tom,

Thanks for your reply.

Yes. macro variable mc_enum_ts has spaces in it, so the error message makes sense that way, since according to log I am trying to use it like this:

set jsondata.docs_&mc_enum_ts.;

However, my code is using another macro variable for the statement that creates the error:

set jsondata.docs_&mc_enumt.;

which is derived from mc_enum_ts like this(by splitting the valiable to multiple values using space as the separator):

%let enumCount = %sysfunc(countw(&mc_enum_ts.,' '));
	%do i=1 %to &enumCount;
	
		%let mc_enumt=%scan(&mc_enum_ts.,&i.,' ');

It looks like SAS is changing my code on the execution or something?

Best regards,

Olli

[ojaro · Accepted solution]

Ok, I managed to figure it out.

My code is running in a macro and I am calling the macro with call execute. Apparently this does not work if you want to populate macro variable inside your main macro with proc sql.

The solution is to use %nrstr as part of call execute as below:

data _null_;
    set mcdtemp.mc_files;
    call symput("mc_file",strip(filename));
    call execute('%nrstr(%parse_datafiles);');
run; 

There is more about this issue at https://stackoverflow.com/questions/50453908/proc-sql-select-into-does-not-create-macro-variable.

[ojaro]

Actually, had to write the above like this, so that the correct filename is passed to the macro:

data _null_;
	set mcdtemp.mc_files;
	call execute('%nrstr(%parse_datafiles('||filename||'));');
run;

[Tom]

Typically you do not need (or want) to add macro quoting to the parameter values also.  Just the macro call. 

Also using the CATS() function to help generate the string to pass to CALL EXECUTE() will automatically trim the trailing spaces from the values of FILENAME variable.  If nothing else it will make the SAS log easier to read.

data _null_;
  set mcdtemp.mc_files;
  call execute(cats('%nrstr(%parse_datafiles)(',filename,');'));
run;