SAS Macro loop datetime in SQL where clause issue

imdickson — Mon, 01 Apr 2019 06:51:52 GMT

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_&period;
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?

Re: SAS Macro loop datetime in SQL where clause issue

andreas_lds — Mon, 01 Apr 2019 07:00:42 GMT

The semicolon in the line

from ncpdm.ncp_loadpf_re_rmr_&period

seems to be misplaced.

Re: SAS Macro loop datetime in SQL where clause issue

Kurt_Bremser — Mon, 01 Apr 2019 07:33:32 GMT

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.

Re: SAS Macro loop datetime in SQL where clause issue

imdickson — Mon, 01 Apr 2019 07:40:29 GMT

Thanks for the advice @Kurt_Bremser

topic SAS Macro loop datetime in SQL where clause issue in SAS Programming

SAS Macro loop datetime in SQL where clause issue

Re: SAS Macro loop datetime in SQL where clause issue

Re: SAS Macro loop datetime in SQL where clause issue

Re: SAS Macro loop datetime in SQL where clause issue