topic Re: Do Loop in Proc SQL in SAS Programming

Do Loop in Proc SQL

kalkebulan — Tue, 30 Aug 2016 16:00:16 GMT

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.

Re: Do Loop in Proc SQL

Astounding — Tue, 30 Aug 2016 16:19:40 GMT

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.

Re: Do Loop in Proc SQL

Shmuel — Tue, 30 Aug 2016 16:29:58 GMT

What OS and what SAS version do you use ?

Try change a line to:

%str(proc sql;);

instead: proc sql;

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

Re: Do Loop in Proc SQL

ballardw — Tue, 30 Aug 2016 20:13:01 GMT

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.