Datetype matching question

[senfonik]

Hi All,

Firstly, i'd like to thanks from now to those who will answer my question.

I got the dates that i will use from a table like the 1st query below. Then, i use this macro variable in another query like 2nd query below. However, i cannot run successfully unfortunately. I 've tried many different approach such as altering column data types, datetime conversion... but i couldnt solve.

Do you have any idea? 😞

PROC SQL noprint;
SELECT
/*dhms(CURRENT_BEGIN_WEEK,0,0,0) format datetime.*/
CURRENT_BEGIN_WEEK
INTO: CURRENT_BEGIN_WEEK
FROM TABLE_A
WHERE ORDER = 38;

%PUT*****&CURRENT_BEGIN_WEEK.;

*LOG;
/* %PUT*****&CURRENT_BEGIN_WEEK.;
*****20SEP2021*/

PROC SQL;
CREATE TABLE DENEME_1617 AS
SELECT
*
FROM
TABLE_B (OBS=100) a
WHERE
a.RZ_TARIHI > &CURRENT_BEGIN_WEEK.;
QUIT;

*log;

/*
proc sql;
28 create table deneme_1617 as
29 select
30 *
31 from
32 O_C_OPR.BOSS_REZERVASYON(OBS=100) a
33 where
34 a.RZ_TARIHI > &CURRENT_BEGIN_WEEK.;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: Line generated by the macro variable "CURRENT_BEGIN_WEEK".
34 20SEP2021
_______
22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, EQT, GE, GET,
GROUP, GT, GTT, HAVING, LE, LET, LT, LTT, NE, NET, OR, ORDER, ^=, |, ||, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

35 QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

*/

/*Date example from table b*/

PROC SQL;
CREATE TABLE DENEME_1657 AS
SELECT RZ_TARIHI
FROM TABLE_B(OBS=1);

/*12NOV2010:00:00:00*/

[PaigeMiller]

When you create a macro and then execute it, the value of macro variables as text is substituted into your code, and after this text substitution you must have valid, legal, working SAS code. So, before this text substitution, you get the following:

where a.RZ_TARIHI > &CURRENT_BEGIN_WEEK.;

and after the text substitution, it looks like this:

where a.RZ_TARIHI > 20SEP2021

This is not valid legal working SAS code. Can you see what needs to be changed to turn the above line into valid legal working SAS code?

Recommendation: first get code to work on one or two instances of your data WITHOUT macros and WITHOUT macro variables before you write a macro. If you don't have valid legal working SAS code without macros and without macro variables, you will never be able to get it to work in a macro.

[Kurt_Bremser · Accepted solution]

Maxim 28: Macro Variables Need No Formats.

Change your SQL:

PROC SQL noprint;
SELECT 
CURRENT_BEGIN_WEEK format=5.
INTO: CURRENT_BEGIN_WEEK 
FROM TABLE_A 
WHERE ORDER = 38;
quit;

and you can use &current_begin_week without any quotes and trailing d.

If you need to work with a datetime value, increase the format to accommodate all digits.