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.
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 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.
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.