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
... View more