Hello, can anyone see in the code below why I might be getting the "ERROR 180-322: Statement is not valid or it is used out of proper order"?
libname x"\\...;
%macro copy_two(qtrname, year, filename);
libname A"\\...;
libname B"\\...;
data B.&filename.&qtrname.&year. (keep=ID cost);/*any other variables to keep go here*/
/*qtr=&qtrname.;
year=&year.;*/
set A.&filename.;
run;
proc sql;
create table B..x&filename.&qtrname.&year. as select *
from B.&filename.&qtrname.&year.;
quit;
%mend copy_two;
%macro loop(start,end);
%do i = &start. %to &end.;
%copy_two(Mar, &i., file);
%copy_two(Jun, &i., file);
%copy_two(Sep, &i., file);
%copy_two(Dec, &i., file);
%end;
%mend loop;
/*specify the years of interest*/
%loop(11,21)
Below code works.
Not sure where in your code you're using libref X or where you define and populate &filename. You certainly haven't shared all the code with us.
Also note that because you pass in string "file" to macro %loop() the only value &filename may have for things to work is file. Not sure if this is what you intended to do.
options dlcreatedir spool mprint symbolgen;
%let filename=FILE;
libname x "c:\temp\a";
data x.&filename;
do id=1 to 5;
cost=id*10;
output;
end;
run;
%macro copy_two(qtrname, year, filename);
libname A "c:\temp\a";
libname B "c:\temp\b";
data B.&filename.&qtrname.&year. (keep=ID cost);/*any other variables to keep go here*/
/*qtr=&qtrname.;
year=&year.;*/
set A.&filename.;
run;
proc sql;
create table B.x&filename.&qtrname.&year. as select *
from B.&filename.&qtrname.&year.;
quit;
%mend copy_two;
%macro loop(start,end);
%do i = &start. %to &end.;
%copy_two(Mar, &i., file);
%copy_two(Jun, &i., file);
%copy_two(Sep, &i., file);
%copy_two(Dec, &i., file);
%end;
%mend loop;
/*specify the years of interest*/
%loop(11,21)
The first possible issue is right in the first statement:
libname x"\\...;
The physical path misses the closing double quote.
But for real diagnostics, we MUST see the whole log of your code, from the beginning to at least up to the first ERROR message.
Use this button to post the log:
Hi Kurt,
Thank you for looking at this; the code includes the " at the end. this is the log error:
180: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 180-322: Statement is not valid or it is used out of proper order.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: The SAS System stopped processing this step because of errors.
Unfortunately, I'm not allowed to show the code exactly or the log, but I hope this helps a bit.
Quote from myself:
But for real diagnostics, we MUST see the whole log of your code, from the beginning to at least up to the first ERROR message.
Emphasis added
Then you will need SAS technical support to help you.
...and as the log tells you add at the beginning of your code: options spool;
Why are there two full stops?
Make sure to turn on the MPRINT option so you can see the SAS code that the macro generates (and that the error message is talking about).
And you definitely cannot have a member name that starts with a period.
create table B..x&filename.&qtrname.&year.
Unless you set option VALIDMEMNAME=EXTEND. And even then you would need to have the macro generate a name literal.
create table B.".x&filename.&qtrname.&year."n
@2222 wrote:
thank you Tom, but i have tried:
proc sql;
create table B.test&filename.&qtrname.&year. as select *
from B.&filename.&qtrname.&year.;
quit;
same error given, so i don't think the period made any difference
So you fixed one error, but you still have another error.
Your code does not look that complicated so it is highly likely the error is actually being generated by something you ran BEFORE the code you posted.
Then eventually you really should set at the very beginning of your program
options mprint symbolgen spool;
As for spool: That's what the SAS log recommends you to do.
Also make sure that you run your code out of a fresh new SAS session (with EG for example disconnect from your session before re-running).
Below code works.
Not sure where in your code you're using libref X or where you define and populate &filename. You certainly haven't shared all the code with us.
Also note that because you pass in string "file" to macro %loop() the only value &filename may have for things to work is file. Not sure if this is what you intended to do.
options dlcreatedir spool mprint symbolgen;
%let filename=FILE;
libname x "c:\temp\a";
data x.&filename;
do id=1 to 5;
cost=id*10;
output;
end;
run;
%macro copy_two(qtrname, year, filename);
libname A "c:\temp\a";
libname B "c:\temp\b";
data B.&filename.&qtrname.&year. (keep=ID cost);/*any other variables to keep go here*/
/*qtr=&qtrname.;
year=&year.;*/
set A.&filename.;
run;
proc sql;
create table B.x&filename.&qtrname.&year. as select *
from B.&filename.&qtrname.&year.;
quit;
%mend copy_two;
%macro loop(start,end);
%do i = &start. %to &end.;
%copy_two(Mar, &i., file);
%copy_two(Jun, &i., file);
%copy_two(Sep, &i., file);
%copy_two(Dec, &i., file);
%end;
%mend loop;
/*specify the years of interest*/
%loop(11,21)
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.