Converting Macro string to Numeric that works with concatenate

[imdickson]

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.

[Patrick]

Your code:

%let aa=%substr(datetime_,7,4); takes the substring from string 'datetime_' and assigns it to macro variable &aa. So that results in assigning the value me_ to macro variable &AA.

The SAS log tells you this clearly:

SYMBOLGEN:  Macro variable AA resolves to me_
SYMBOLGEN:  Macro variable BB resolves to et

You then try to use this string "me_" in a SAS putn() function. This could only work if &aa.&bb would resolve to a string which is the name of an existing SAS variable - and this variable is of the correct type with an appropriate value to be used within the putn() function. This is in your case obviously not the case as the SAS log clearly tells you: 
ERROR: Argument 1 to function PUTN referenced by th...

I don't fully understand what you're trying to achieve so can't provide a solution here.  

[RW9]

Problem 1:

"i have a script that works fine in normal SQL statement without macro."

Answer:

Learn Base SAS, you will find coding far simpler and easier rather than trying to force it through a third party software addon.  For example, statement 1 is simply:

proc sort data=hpsncp.scada_mrcc_mv out=mrcc_asset nodupkey;
  by substationcode substationname substationtype voltagetype;
  where cats(substr(datetime_,7,4),substr(datetime_,4,2)="&period.";
run;

Problem 2:
"The reason why i put in macro now is to handle period checking whereby different period will have different column from different source table."

Answer: 

Re-assess your data modelling.  If you find you are having to resort to macro programming to handle different data, then your underlying data is badly modelled.  I can't tell exactly without seeing data, but if your calling the same macro with different groups, put those groups into your data, and then just use a by <groupvariable> statement, you will find it is a lot faster, simpler coding, and more efficient.

[Kurt_Bremser · Accepted solution]

Very simple.

where substr(datetime_,7,4) || substr(datetime_,4,2)) = "&period";

Count the opening brackets vs. the closing brackets.

[Quentin]

Looks to me like in code 1 there is an extra close parenthesis that is causing the error:
where substr(datetime_,7,4) || substr(datetime_,4,2)) = "&period";

Note that in the log it indicates that the close parenthesis symbol is not recognized.

[Tom]

Looks like you broke your SAS code by sticking in unneeded percent signs to convert it into macro code.

Sounds like you just want to use a simple %IF to let the macro to decide which block of SAS code to run, not convert the SAS code into macro code.

%macro srcnmchk();
proc sql noprint;
  create table mrcc_asset as
    select distinct substationcode length 50 format $50. 
    , substationname length 50 format $50.
    , substationtype length 50 format $50. 
    , voltagetype length 10 format $10. 
    , 'MRCC' as src_nm length 10
    from hpsncp.&srcnm._mrcc_mv
%if &srcnm eq scada %then %do;
    where substr(datetime_,7,4)||substr(datetime_,4,2) = "&period"
%end;
%else %do;
    where substr(filedate,1,6)="&period"
%end;
  ;
quit;
%mend;

%srcnmchk;

[ballardw]

A possibly not quite silly question: is your variable DATETIME a character value? If not and the value is an actual SAS datetime value then you likely are not going to get the desired result:

Example with a SAS datetime value.

data example;
   datetime_='01JAN2019:12:12:12'dt;
   format datetime_ datetime20.;
   x=substr(datetime_,7,4);
   y=substr(datetime_,4,2); 
run; 

Which yields 9639 and 86.

If your datetime_ is character, why? SAS provides a large number of tools to extract pieces of information from date, datetime and time valued variables. It appears that you might be attempting to compare a year of 2018 and month of 08.

data example;
   datetime_='01AUG2018:12:12:12'dt;
   format datetime_ datetime20.;
   if put(datepart(datetime_),yymmn6.)='201808' then put "Found It!";
run; 

For one example. I could create a custom format for use with DATETIME values to show only year and month without the extraction of the date portion of a datetime value.