DATA Step, Macro, Functions and more

Processing submitted statements

Accepted Solution Solved
Reply
Super Contributor
Posts: 673
Accepted Solution

Processing submitted statements

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;


Accepted Solutions
Solution
‎09-23-2017 10:53 PM
Super User
Super User
Posts: 7,074

Re: Processing submitted statements

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;

View solution in original post


All Replies
Super User
Posts: 19,855

Re: Processing submitted statements

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;
Frequent Contributor
Posts: 149

Re: Processing submitted statements


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.

Super User
Posts: 19,855

Re: Processing submitted statements

Posted in reply to error_prone

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. 

Super User
Posts: 11,343

Re: Processing submitted statements

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.

 

Super User
Posts: 19,855

Re: Processing submitted statements

[ Edited ]

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;

 

 

 

Super Contributor
Posts: 673

Re: Processing submitted statements

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.

 

Solution
‎09-23-2017 10:53 PM
Super User
Super User
Posts: 7,074

Re: Processing submitted statements

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;
Respected Advisor
Posts: 4,173

Re: Processing submitted statements

@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;
Super User
Posts: 19,855

Re: Processing submitted statements

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

Respected Advisor
Posts: 4,173

Re: Processing submitted statements

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 161 views
  • 4 likes
  • 6 in conversation