SAS Macro loop datetime in SQL where clause issue

[imdickson]

I am having a script that as SQL statement in which where clause is to do filtering based on data in datetime format that i assign.

Code:

%macro datelist();

proc sql;
select datetime into :redatetime1-:redatetime&SysMaxLong
from lp2
where datetime ne .;
select count(*) into :cnt
from lp2
where datetime ne .;
quit;

proc sql;
create table test as
select * 
from ncpdm.ncp_loadpf_re_rmr_.
where corrected_endtime = "&redatetime1"dt;
quit;

%mend;
%datelist;

Here's the log:

MPRINT(DATELIST):   proc sql;
MPRINT(DATELIST):   create table test as select * from ncpdm.ncp_loadpf_re_rmr_201801;
MVA_DSIO.OPEN_CLOSE| _DISARM|         STOP| _DISARM| 2019-04-01T14:46:25,531+08:00| _DISARM| WorkspaceServer| _DISARM| | _DISARM| | _DISARM| | _DISARM| 27959296| _DISARM| 13| _DISARM| 26| _DISARM| 29320| _DISARM| 1052760| _DISARM| | _DISARM| | _DISARM| | 
_DISARM| | _DISARM| | _DISARM| | _ENDDISARM 
NOTE: Compressing data set WORK.TEST decreased size by 7.03 percent. 
      Compressed is 238 pages; un-compressed would require 256 pages.
MVA_DSIO.OPEN_CLOSE| _DISARM|         STOP| _DISARM| 2019-04-01T14:46:25,537+08:00| _DISARM| WorkspaceServer| _DISARM| | _DISARM| | _DISARM| | _DISARM| 27959296| _DISARM| 13| _DISARM| 26| _DISARM| 30472| _DISARM| 1053912| _DISARM| | _DISARM| | _DISARM| | 
_DISARM| | _DISARM| | _DISARM| | _ENDDISARM 
NOTE: Table WORK.TEST created, with 209040 rows and 8 columns.

WARNING: This SAS global statement is not supported in PROC SQL. It has been ignored.
MPRINT(DATELIST):   where corrected_endtime = "01JAN18:01:00:00"dt;
MPRINT(DATELIST):   quit;

What did i do wrong?

[andreas_lds · Accepted solution]

The semicolon in the line

from ncpdm.ncp_loadpf_re_rmr_&period

seems to be misplaced.

[Kurt_Bremser]

Step one of macro development: start with working SAS code.

If you violated that principle, test your code outside the macro, to get a more meaningful log. Then it would be easier to detect the misplaced semicolon.

[imdickson]

Thanks for the advice @Kurt_Bremser