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

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

3 REPLIES 3
Astounding
PROC Star

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.

Shmuel
Garnet | Level 18

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

ballardw
Super User

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.

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
  • 3 replies
  • 3222 views
  • 0 likes
  • 4 in conversation