topic Re: Processing submitted statements in SAS Programming

Processing submitted statements

SASPhile — Thu, 21 Sep 2017 15:54:58 GMT

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;

Re: Processing submitted statements

Reeza — Thu, 21 Sep 2017 16:16:03 GMT

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;

Re: Processing submitted statements

ballardw — Thu, 21 Sep 2017 16:18:26 GMT

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.

Re: Processing submitted statements

Reeza — Thu, 21 Sep 2017 16:37:16 GMT

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;

Re: Processing submitted statements

error_prone — Thu, 21 Sep 2017 17:10:53 GMT

@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.

Re: Processing submitted statements

Reeza — Thu, 21 Sep 2017 17:22:02 GMT

@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.

Re: Processing submitted statements

SASPhile — Sat, 23 Sep 2017 22:40:31 GMT

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.

Re: Processing submitted statements

Tom — Sun, 24 Sep 2017 00:44:13 GMT

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;

Re: Processing submitted statements

Patrick — Sun, 24 Sep 2017 00:46:36 GMT

@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;

Re: Processing submitted statements

Reeza — Sun, 24 Sep 2017 01:46:36 GMT

@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.

Re: Processing submitted statements

Patrick — Sun, 24 Sep 2017 01:58:07 GMT

@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.

Re: Processing submitted statements

doreamonjin — Mon, 09 Apr 2018 11:19:37 GMT

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

Re: Processing submitted statements

Reeza — Mon, 09 Apr 2018 14:55:13 GMT

@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;

Re: Processing submitted statements

doreamonjin — Mon, 09 Apr 2018 15:23:34 GMT

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?

Re: Processing submitted statements

Reeza — Mon, 09 Apr 2018 15:28:59 GMT

@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.