DATA Step, Macro, Functions and more

Do Loop in Proc SQL

Accepted Solution Solved
Reply
Occasional Learner
Posts: 1
Accepted Solution

Do Loop in Proc SQL

Hello:

 

I have 6 separate datasets. I want to upload each of these datasets and create 6 different oracle tables. I have a macro in which I am attempting to execute Do loop in Proc SQL:

 

%let count = 6;

libname lob '/xxx/xxx/xxx/Data';

libname khalke oracle user=&xxx password="&xxx" path="&xxx" schema=&xxx;

%Macro fileupload;

proc sql;

%do i = 1 %to &count;

create table khalke.test_xxx_d209file_T&count as

select *

from lob.test_xxx_d209file&count

order by actnmbr;

%end;

quit;

%mend;

%fileupload;

 

However, I am getting the following error message:

 

%let count = 6;

libname lob '/xxx/xxx/xxx/Data';

NOTE: Libref LOB was successfully assigned as follows:

Engine: V9

Physical Name: /xxx/xxx/xxx/Data

libname khalke oracle user=&xxx password="&xxx" path="&xxx"

! schema=&xxx;

NOTE: Libref KHALKE was successfully assigned as follows:

Engine: ORACLE

Physical Name: WFPROD

%Macro fileupload;

proc sql;

%do i = 1 %to &count;

create table khalke.test_xxxx_d209file_T&count as

select *

from lob.test_xxxx_d209file&count

order by actnmbr;

%end;

quit;

%mend;

ERROR: There were 1 unclosed %DO statements. The macro FILEUPLOAD will not be compiled.

ERROR: A dummy macro will be compiled.

 

%fileupload;

-

180

WARNING: Apparent invocation of macro FILEUPLOAD not resolved.

ERROR 180-322: Statement is not valid or it is used out of proper order.

 

Thank you.

 

 


Accepted Solutions
Solution
‎08-31-2016 10:58 AM
Super User
Posts: 11,343

Re: Do Loop in Proc SQL

Posted in reply to kalkebulan

You may want to reconsider this code snippet:

create table khalke.test_xxx_d209file_T&count as

select *

from lob.test_xxx_d209file&count

That is creating the same file count times (and may cause some timing issues)

 

I suspect that you meant

create table khalke.test_xxx_d209file_T&i as

select *

from lob.test_xxx_d209file&i

to use the i counter to change table names.

 

 

Run your code with OPTIONS MPRINT;

before calling the macro. The log will likely provide better information as to where the problem is occuring.

View solution in original post


All Replies
Super User
Posts: 5,518

Re: Do Loop in Proc SQL

Posted in reply to kalkebulan

This code looks fine.

 

There must be complications, like another macro that contains an unclosed %DO block that also contains this code. 

 

To help in the diagnosis, try changing the %MEND statement so it specifies which macro definition is ending:

 

%mend fileupload;

 

That can only help, never hurts.

Trusted Advisor
Posts: 1,586

Re: Do Loop in Proc SQL

Posted in reply to kalkebulan

What OS and what SAS version do you use ?

 

Try change a line to:

   %str(proc sqlSmiley Wink;    

 

instead: proc sql;

 

I have tried using %do ... %end in a proc sql, similar to your code, and had no ERROR like your

Solution
‎08-31-2016 10:58 AM
Super User
Posts: 11,343

Re: Do Loop in Proc SQL

Posted in reply to kalkebulan

You may want to reconsider this code snippet:

create table khalke.test_xxx_d209file_T&count as

select *

from lob.test_xxx_d209file&count

That is creating the same file count times (and may cause some timing issues)

 

I suspect that you meant

create table khalke.test_xxx_d209file_T&i as

select *

from lob.test_xxx_d209file&i

to use the i counter to change table names.

 

 

Run your code with OPTIONS MPRINT;

before calling the macro. The log will likely provide better information as to where the problem is occuring.

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 1205 views
  • 0 likes
  • 4 in conversation