BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ojaro
SAS Employee

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

1 ACCEPTED SOLUTION

Accepted Solutions
ojaro
SAS Employee

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.

View solution in original post

5 REPLIES 5
Tom
Super User Tom
Super User

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
SAS Employee

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
SAS Employee

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
SAS Employee

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
Super User Tom
Super User

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 975 views
  • 0 likes
  • 2 in conversation