Hi all, i have a script that works fine in normal SQL statement without macro. The reason why i put in macro now is to handle period checking whereby different period will have different column from different source table. My period is a macro variable with the possible value of 201808 and 201812 I have 2 set of testing code. Code 1: %let period=201808;
%let srcnm=scada;
%macro srcnmchk();
%if &srcnm=scada %then %do;
proc sql noprint;
create table mrcc_asset as
select distinct substationcode length 50 format $50. informat $50.
, substationname length 50 format $50. informat $50.
, substationtype length 50 format $50. informat $50.
, voltagetype length 10 format $10. informat $10.
, 'MRCC' as src_nm length 10
from hpsncp.scada_mrcc_mv
where substr(datetime_,7,4) || substr(datetime_,4,2)) = "&period";
quit;
%end;
%mend;
%srcnmchk; Log for code 1: SYMBOLGEN: Macro variable SRCNM resolves to scada
MLOGIC(SRCNMCHK): %IF condition &srcnm=scada is TRUE
MPRINT(SRCNMCHK): *Extract distinct asset from doms datalake;
MPRINT(SRCNMCHK): proc sql noprint;
SYMBOLGEN: Macro variable PERIOD resolves to 201808
NOTE: Line generated by the invoked macro "SRCNMCHK".
54 ) = "&period"; quit;
_
22
200
MPRINT(SRCNMCHK): create table mrcc_asset as select distinct substationcode length 50 format $50. informat $50. , substationname length 50 format $50. informat $50. , substationtype length 50 format $50. informat $50. , voltagetype length 10 format $10.
informat $10. , 'MRCC' as src_nm length 10 from hpsncp.scada_mrcc_mv where substr(datetime_,7,4) || substr(datetime_,4,2)) = "201808";
ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, EXCEPT, GE, GET, GROUP, GT, GTT, HAVING, IN, INTERSECT, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR,
ORDER, OUTER, UNION, ^, ^=, |, ||, ~, ~=.
ERROR 200-322: The symbol is not recognized and will be ignored. Here's my code 2: %macro srcnmchk(); proc sql noprint;
create table mrcc_asset as
select distinct substationcode length 50 format $50. informat $50.
, substationname length 50 format $50. informat $50.
, substationtype length 50 format $50. informat $50.
, voltagetype length 10 format $10. informat $10.
, 'MRCC' as src_nm length 10
from hpsncp.&srcnm._mrcc_mv
%if &srcnm eq scada %then %do;
%let aa=%substr(datetime_,7,4);
%let bb=%substr(datetime_,4,2);
where %sysfunc(putn(&aa.&bb.,8.)) = "&period";
/* catx(%substr(datetime_,7,4),%substr(datetime_,4,2)) = "&period"; */
%end;
%else %do;
where substr(filedate,1,6)="&period";
%end; %mend; %srcnmchk; It won't work. The log for code 2: SYMBOLGEN: Macro variable AA resolves to me_
SYMBOLGEN: Macro variable BB resolves to et
ERROR: Argument 1 to function PUTN referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.
ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list. Execution of %SYSCALL statement or %SYSFUNC or %QSYSFUNC function reference is terminated.
SYMBOLGEN: Macro variable PERIOD resolves to 201808
NOTE 137-205: Line generated by the invoked macro "SRCNMCHK".
228 where %sysfunc(putn(&aa.&bb.,8.)) = "&period";
_
22
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, BTRIM, INPUT, PUT, SUBSTRING, USER. What could be wrong? Stuck here for 2 hours. Your advice is very much needed.
... View more