BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SASPhile
Quartz | Level 8

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

14 REPLIES 14
Reeza
Super User

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
Barite | Level 11

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

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

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

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
Quartz | Level 8

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

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
Fluorite | Level 6

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

Reeza
Super User

@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
Fluorite | Level 6

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

@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
Opal | Level 21

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

@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
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 14 replies
  • 3300 views
  • 4 likes
  • 7 in conversation