Processing submitted statements

[SASPhile]

There are 11 datasets and I want to export as .tsv files.
OUTFILE1 resolves and exports the dataset to a tsv file.
there is no progress afterward,SAS is stuck and displays processing submitted statements for a very long time (6hrs).

%macro prepare_outfiles;
proc sql ;
create table mytables as
select distinct memname
from dictionary.tables
where lowcase(libname) = "mylib"
order by memname ;
quit ;
data _null_;
set mytables end = eof;
if eof then call symputx('e_o_f',_n_);
_export = cats('outfile',_n_);
call symputx(_export,memname);
run;

%do
i= 1 %to &e_o_f.;
%put &&outfile&i.;

proc export data=mylib..&&outfile&i.
outfile="&tsv_output.\&&outfile&i...tsv"
dbms=tab replace;
run;
%end;
%mend prepare_outfiles;
%prepare_outfiles;

[Reeza]

This shouldn't be a macro in my opinion. 

Rather than use a loop to call PROC EXPORT use CALL EXECUTE instead. It's much cleaner and easier. 

Here's your code formatted for legibility. If you're using SAS EG, hightlight your code and press CTRL+I or in SAS Studio its' the second last icon in the menu bar.

%macro prepare_outfiles;
	proc sql ;
		create table mytables as select distinct memname from dictionary.tables where 
			lowcase(libname)="mylib" order by memname;
	quit;

	data _null_;
		set mytables end=eof;

		if eof then
			call symputx('e_o_f', _n_);
		_export=cats('outfile', _n_);
		call symputx(_export, memname);
	run;

	%do i=1 %to &e_o_f.;
		%put &&outfile&i.;

		proc export data=mylib..&&outfile&i.
			outfile="&tsv_output.\&&outfile&i...tsv" dbms=tab replace;
		run;

	%end;
%mend prepare_outfiles;

%prepare_outfiles;

[error_prone]

---

@Reeza wrote:

This shouldn't be a macro in my opinion. 

Rather than use a loop to call PROC EXPORT use CALL EXECUTE instead. It's much cleaner and easier. 

---

Can't fully agree. With call execute you avoid coding the loop but make the code to be executed more difficult to read. For a single proc export this is bearable, but not for anything longer than two lines of code.

[Reeza]

---

@error_prone wrote:

---

@Reeza wrote:

This shouldn't be a macro in my opinion. 

Rather than use a loop to call PROC EXPORT use CALL EXECUTE instead. It's much cleaner and easier. 

---

Can't fully agree. With call execute you avoid coding the loop but make the code to be executed more difficult to read. For a single proc export this is bearable, but not for anything longer than two lines of code.

---

@error_prone I sort of agree. The comments above are in context of the question the OP posed, but for longer code I probably wouldn't use CALL EXECUTE directly. I'd likely wrap the code in a macro and then use CALL EXECUTE to call it as needed. 

[ballardw]

You have two issues with . that maybe causing this:

proc export data=mylib..&&outfile&i.

Since MYLIB is not a macro variable this is creating: data=mylib..resolvedfirstdataset  so you should be getting lots of errors.

Second in

outfile="&tsv_output.\&&outfile&i...tsv"

You have 3 periods wher you want 2.

Also you can replace the proc sql and data step with this

proc sql noprint;
select distinct memname into : outfile1- :outfile100
from dictionary.tables
where lowcase(libname) = "mylib"
order by memname ;
quit ;

%let e_o_f = &sqlobs;

Proc sql will select values into macro variables with the INTO instruction the way it is dont above will place each memname into a separate macro variable (as long as there are not more than 100 in the library) and only use as many as are needed if less than 100. Proc Sql also creates an automatic macro variable that has the number of records returned by the previous sql operation. So you can assign it to your counter.

[Reeza]

A note that you don't need the last entry here, this also works to create just the list of macro variables you need:

select distinct memname into : outfile1- 

Example:

 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 55         
 56         proc sql noprint;
 57         select name into :names1 -
 58         from sashelp.class;
 59         quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 60         
 61         %put &names1;
 Alfred
 62         %put &names19;
 William
 63      

Like I mentioned, a macro is overkill here. See the example below. The STR variable becomes the PROC EXPORT you need and then CALL EXECUTE will execute the code. A single data step is sufficient here. 

The STR variable is not created correctly, I'll leave that as an exercise to you.

data export_list;
	set sashelp.vtable;
	where lowcase(libname)='sashelp';
	
	length str $200.;
	
	*not correct right now , but you can customize this to match your needs;
	str=catt('proc export data=mylib.', trim(memname), ' outfile=', trim(memname), 
		"_output\", trim(memname), '.tsv dbms=tab replace;  run;');
		
	*once the proc export is correct then uncomment this;
	*call execute(str);
	
	keep libname memname str;
run;

[SASPhile]

Tried to exapnd call execute to sort multiple datasets,with by variables and dataset names in dataset.the call execute doest seem to work as expected.

data _tablelist;
input dsn $1-2 ordby $4-65;
datalines;
ae start_date end_date
;
run;

data _null;
set _tablelist;
call execute ("proc sort data=libref.'||strip(dsn)||' 'out=' dsn;
'by' ordby; run;");
run;

proc sort data=libref.'||strip(dsn)||' 'out=' dsn; 'by' ordby; run;
---------------- ----
22 180
76
ERROR: Invalid data set name libref..

ERROR 22-322: Syntax error, expecting one of the following: ;, (, ASCII, BUFFNO, DANISH, DATA,
DATECOPY, DETAILS, DIAG, DUPOUT, EBCDIC, EQUALS, FINNISH, FORCE, IN, ISA, L, LEAVE,
LIST, MESSAGE, MSG, NATIONAL, NODUP, NODUPKEY, NODUPKEYS, NODUPLICATE,
NODUPLICATES, NODUPREC, NODUPRECS, NODUPS, NOEQUALS, NORWEGIAN, NOTHREADS,
NOUNIKEY, NOUNIKEYS, NOUNIQUEKEY, NOUNIQUEKEYS, NOUNIQUEREC, NOUNIQUERECS,
NOUNIREC, NOUNIRECS, OSA, OUT, OVERWRITE, PAGESIZE, PRESORTED, PSIZE, REVERSE,
SIZE, SORTSEQ, SORTSIZE, SORTWKNO, SWEDISH, T, TAGSORT, TECH, TECHNIQUE, TESTHSI,
THREADS, UNIOUT, UNIQUEOUT, WKNO, WORKNO.

[Tom · Accepted solution]

You have unbalanced quotes.  If find the CAT series of functions easier to deal with than the || operator.

data _tablelist;
  length dsn $32 ;
  input dsn ordby $62. ;
datalines;
ae start_date end_date
;

data _null;
  set _tablelist;
  call execute (catx(' '
,'proc sort data=', cats('libref.',dsn)
,'out=',dsn,';'
,'by',ordby,'; run;'
));
run;

[doreamonjin]

Could you explain what is the 'unbalanced quotes'? I have the same problem as 'processing submitted statements' for a very long time.

[Reeza]

@doreamonjin It means you had an open quote in one location and didn't have a close quote somewhere:

x = 'some string ; *note that this is missing a closing quote;
y = 'mismatched quotes"; *note that in this example, single and double quotes are mixed which does not work;

[doreamonjin]

Thank you so much🙏👏🏻👍but this might not be the problem of my macro. I have a regression macro and when it is dealing with two variables, it usually takes around 20 min. Do you know what might cause this problem?

[Reeza]

@doreamonjin Please create a new thread with the details of your own question. Include as much detail as possible including your code and the log. 

[Patrick]

@SASPhile

You've got the quoting wrong for example strip(dsn) doesn't get resolved.

The log actually tells you what call execute() tried to execute. This line in your log needs to be fully valid SAS syntax.

proc sort data=libref.'||strip(dsn)||' 'out=' dsn; 'by' ordby; run;

[Reeza]

@SASPhile And this is why I recommend the method of building a STR (string variable) first and then passing it to CALL EXECUTE. Then you can first validate your string is correct SAS code. If you cannot paste the code in the STR variable directly into your SAS editor and run it, your code is incorrect. 

It also seems like your original question has been answered. 

[Patrick]

@SASPhile

And to add to @Reeza's 2nd post: Please start marking the answer which helped you most as correct answer/solution. That's one of the courtesy rules in the SAS forums.

I just went through your past questions and it appears you haven't used this forum functionality yet.