ERROR 180-322: Statement is not valid or it is used out of proper order (MACRO proc sql)

[reemm225]

I am trying to combine several files of the same type and combine them with another file. I am using a macro and proc sql. The macro runs and creates the files however, I get the ERROR 180-322: Statement is not valid or it is used out of proper order. I don't have open comments in my code, I am running this code in a script that his this code alone. Any thoughts?

This is the full error:

NOTE: Line generated by the invoked macro "THERAPY".
15 data Therapy; set ther1-ther&i ; by patid date ; run;
--
180

%macro Therapy ;
%do i=1 %to 6;

	proc sql;
	create table ther&i as
	select ther.date, a.patid, a.male, a.yob, a.mob, a.regstart
	from rawdata.Therapy&i as ther, ppidata.keep2 as a
	where ther.id=a.patid
	order by a.patid,ther.date;
	quit;
	%end;

	
 	%do i=1 %to 6;
	data Therapy;
	set ther1-ther&i ;
	by patid date ;
	run;
	%end;
	
%mend Therapy;

%Therapy;

[PaigeMiller]

When you want to debug a macro, you need to turn on the proper options.

options mprint;

Then run the code and look at the resulting log and see if you can figure out what is wrong.

If you can't figure it out, please show us the entire LOG for this macro. Do not select parts of the log to show us and then not show us other parts. Do not show us errors detached from the code that caused the errors as you have done.

It is critically important to format the LOG properly by copying it as text and pasting it into the window that appears when you click on the </> icon here in the SAS Communities. DO NOT SKIP THIS STEP.

[reemm225]

Thanks so much for your reply. I ran the options mprint. I still can't figur eout what the problem is. I attached the entire log as advised. Thank you!

68   options mprint;

69   %macro Therapy ;
70   %do i=1 %to 6;
71
72       proc sql;
73       create table ther&i as
74       select ther.date, a.patid, a.male, a.yob, a.mob, a.regstart
75       from rawdata.Therapy&i as ther, ppidata.keep2 as a
76       where ther.id=a.patid
77       order by a.patid,ther.date;
78       quit;
79       %end;
80
81       %do i=1 %to 6;
82       data Therapy;
83       set ther1-ther&i ;
84       by patid date ;
85       run;
86       %end;
87
88   %mend Therapy;

89   %Therapy;
MPRINT(THERAPY):   proc sql;
MPRINT(THERAPY):   create table ther1 as select ther.date, a.patid, a.male, a.yob, a.mob,
a.regstart from rawdata.Therapy1 as ther, ppidata.keep2 as a where ther.id=a.patid order by
a.patid,ther.date;
NOTE: Table WORK.THER1 created, with 113413 rows and 6 columns.

MPRINT(THERAPY):   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           3.63 seconds
      cpu time            0.78 seconds


MPRINT(THERAPY):   proc sql;
MPRINT(THERAPY):   create table ther2 as select ther.date, a.patid, a.male, a.yob, a.mob,
a.regstart from rawdata.Therapy2 as ther, ppidata.keep2 as a where ther.id=a.patid order by
a.patid,ther.date;
NOTE: Table WORK.THER2 created, with 110672 rows and 6 columns.

MPRINT(THERAPY):   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           3.17 seconds
      cpu time            0.54 seconds


MPRINT(THERAPY):   proc sql;
MPRINT(THERAPY):   create table ther3 as select ther.date, a.patid, a.male, a.yob, a.mob,
a.regstart from rawdata.Therapy3 as ther, ppidata.keep2 as a where ther.id=a.patid order by
a.patid,ther.date;
NOTE: Table WORK.THER3 created, with 93627 rows and 6 columns.

MPRINT(THERAPY):   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           2.85 seconds
      cpu time            0.61 seconds


MPRINT(THERAPY):   proc sql;
MPRINT(THERAPY):   create table ther4 as select ther.date, a.patid, a.male, a.yob, a.mob,
a.regstart from rawdata.Therapy4 as ther, ppidata.keep2 as a where ther.id=a.patid order by
a.patid,ther.date;
NOTE: Table WORK.THER4 created, with 107189 rows and 6 columns.

MPRINT(THERAPY):   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           2.69 seconds
      cpu time            0.54 seconds


MPRINT(THERAPY):   proc sql;
MPRINT(THERAPY):   create table ther5 as select ther.date, a.patid, a.male, a.yob, a.mob,
a.regstart from rawdata.Therapy5 as ther, ppidata.keep2 as a where ther.id=a.patid order by
a.patid,ther.date;
NOTE: Table WORK.THER5 created, with 87705 rows and 6 columns.

MPRINT(THERAPY):   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           2.82 seconds
      cpu time            0.47 seconds


MPRINT(THERAPY):   proc sql;
MPRINT(THERAPY):   create table ther6 as select ther.date, a.patid, a.male, a.yob, a.mob,
a.regstart from rawdata.Therapy6 as ther, ppidata.keep2 as a where ther.id=a.patid order by
a.patid,ther.date;
NOTE: Table WORK.THER6 created, with 65155 rows and 6 columns.

MPRINT(THERAPY):   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           1.58 seconds
      cpu time            0.29 seconds


NOTE: Line generated by the invoked macro "THERAPY".
13      
       -
       180
ERROR 180-322: Statement is not valid or it is used out of proper order.

MPRINT(THERAPY):     data Therapy;
NOTE: Line generated by the invoked macro "THERAPY".
15        data Therapy;     set ther1-ther&i ;     by patid date ;     run;
                            ---
                            180
ERROR 180-322: Statement is not valid or it is used out of proper order.

MPRINT(THERAPY):   set ther1-ther1 ;

NOTE: Line generated by the invoked macro "THERAPY".
15        data Therapy;     set ther1-ther&i ;     by patid date ;     run;
                                                   --
                                                   180
ERROR 180-322: Statement is not valid or it is used out of proper order.

MPRINT(THERAPY):   by patid date ;
MPRINT(THERAPY):   run;
MPRINT(THERAPY):   data Therapy;
MPRINT(THERAPY):   set ther1-ther2 ;
MPRINT(THERAPY):   by patid date ;
MPRINT(THERAPY):   run;

NOTE: There were 113413 observations read from the data set WORK.THER1.
NOTE: There were 110672 observations read from the data set WORK.THER2.
NOTE: The data set WORK.THERAPY has 224085 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.19 seconds
      cpu time            0.18 seconds


MPRINT(THERAPY):   data Therapy;
MPRINT(THERAPY):   set ther1-ther3 ;
MPRINT(THERAPY):   by patid date ;
MPRINT(THERAPY):   run;

NOTE: There were 113413 observations read from the data set WORK.THER1.
NOTE: There were 110672 observations read from the data set WORK.THER2.
NOTE: There were 93627 observations read from the data set WORK.THER3.
NOTE: The data set WORK.THERAPY has 317712 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.27 seconds
      cpu time            0.26 seconds


MPRINT(THERAPY):   data Therapy;
MPRINT(THERAPY):   set ther1-ther4 ;
MPRINT(THERAPY):   by patid date ;
MPRINT(THERAPY):   run;

NOTE: There were 113413 observations read from the data set WORK.THER1.
NOTE: There were 110672 observations read from the data set WORK.THER2.
NOTE: There were 93627 observations read from the data set WORK.THER3.
NOTE: There were 107189 observations read from the data set WORK.THER4.
NOTE: The data set WORK.THERAPY has 424901 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.37 seconds
      cpu time            0.35 seconds


MPRINT(THERAPY):   data Therapy;
MPRINT(THERAPY):   set ther1-ther5 ;
MPRINT(THERAPY):   by patid date ;
MPRINT(THERAPY):   run;

NOTE: There were 113413 observations read from the data set WORK.THER1.
NOTE: There were 110672 observations read from the data set WORK.THER2.
NOTE: There were 93627 observations read from the data set WORK.THER3.
NOTE: There were 107189 observations read from the data set WORK.THER4.
NOTE: There were 87705 observations read from the data set WORK.THER5.
NOTE: The data set WORK.THERAPY has 512606 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.44 seconds
      cpu time            0.43 seconds


MPRINT(THERAPY):   data Therapy;
MPRINT(THERAPY):   set ther1-ther6 ;
MPRINT(THERAPY):   by patid date ;
MPRINT(THERAPY):   run;

NOTE: There were 113413 observations read from the data set WORK.THER1.
NOTE: There were 110672 observations read from the data set WORK.THER2.
NOTE: There were 93627 observations read from the data set WORK.THER3.
NOTE: There were 107189 observations read from the data set WORK.THER4.
NOTE: There were 87705 observations read from the data set WORK.THER5.
NOTE: There were 65155 observations read from the data set WORK.THER6.
NOTE: The data set WORK.THERAPY has 577761 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.46 seconds
      cpu time            0.45 seconds

[PaigeMiller]

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

I am guessing that you have an unprintable character in this part of your code, between the first %END and the second %DO

Delete these lines, see if the problem goes away.

[reemm225]

This did not work. I still don't know what the problem was. but I shortened my macro as suggested and it worked

[ballardw]

Unless you really want  to waste a lot runtime overwriting the same output data set 6 times you should simplify this:

   %do i=1 %to 6;
82       data Therapy;
83       set ther1-ther&i ;
84       by patid date ;
85       run;
86       %end;

This would create a data set Therapy with Ther1. Then overwrite it with a Therapy that contains Ther1 and Ther2. Then overwrite that with a set containing Ther1, Ther2 and Ther3, repeat more times.

Basically, you do not want the loop %do i=1 %to 6 around that data step.

[reemm225]

Thank you so much! This solved it. The run time as you said was much shorter.

[Astounding · Accepted solution]

While several excellent points have been made, the bottom line is this.  Reduce your macro so it includes just the top loop:

%macro Therapy ;
%do i=1 %to 6;

	proc sql;
	create table ther&i as
	select ther.date, a.patid, a.male, a.yob, a.mob, a.regstart
	from rawdata.Therapy&i as ther, ppidata.keep2 as a
	where ther.id=a.patid
	order by a.patid,ther.date;
	quit;
	%end;
	
%mend Therapy;

%Therapy;

Then combine your data sets afterwards, without any macro language:

data Therapy;
set ther1-ther6;
by patid date ;
run;

That should be more efficient, easier to understand, and (if any errors remain) easier to debug.

[reemm225]

This worked! Thank you for the proposing the solution.