Hi ,
Please someone could tell how is the correct way for this code?
I have three conditions to check on a IF statement ( STEP 2 ) but it gives me an error msg.
Many thanks in advance , please see attached both code and log .
/*-----------------MACROS DE DATA ------------------*/
%let today=%sysfunc(today());
%let currdt=%sysfunc(datetime());
data _null_;
date2=intnx("month",&today.,-1,'end');
put date2=;
call symputx('DATE2',put(date2 ,date9.));
month_id = intck('month','01jan1990'd,today());
put month_id=;
call symputx('month_id',month_id);
run;
%put &=DATE2;
%put &=month_id;
/*STEP 1 */
proc sql;
drop table IRM.TESTPAYM;
quit;
Proc SQL;
connect to teradata
(user=&teradata_user. password=&teradata_pwd. server = 'edwprod' database = 'nuc_pl_user_view');
Create table IRM.TESTPAYM as select * from connection to teradata(
select a.ID, a.maxmargin_dt, b.maxmodelmart_d, c.maxexploit_d from
(
select
1 as ID,
max(month_end_dt) as maxmargin_dt
from nuc_pl_user_view.pm_margin_stack) as A
left join (
select
1 as ID,
max(month_id) as maxmodelmart_d
from Insights_rm.Consumer_Model_Mart
WHERE internal_CA_model_id IN (202,203,204,205,210,220,217,216,234,235,219,223,225,226,228,229,232)) as B
on a.ID = b.ID
left join (
select
1 as ID,
max(month_end_dt) as maxexploit_d
from PROFITABILITY_SANDBOX.exploitation_layer_pm ) as C
on a.ID = c.ID
);
disconnect from teradata ;
QUIT;
/* STEP 2 */
proc sql;
drop table IRM.TESTPAYM2;
quit;
DATA IRM.TESTPAYM2;
SET IRM.TESTPAYM;
IF ((maxmargin_dt >=(&date2.)) and
(maxmodelmart_d ^= (&month_id.))and
(maxexploit_d >=(&date2.)) )
then export_flag='Y';
else export_flag='N';
call symputx('export_flag', export_flag);
run;
*check value of macro variable;
%PUT Export_Flag = &export_flag;
_____________________________________________
/* LOG FILE FOR THIS CODE WITH ERROR MSG */
23 GOPTIONS ACCESSIBLE;
24 /*-----------------MACROS DE DATA ------------------*/
25
26 %let today=%sysfunc(today());
27 %let currdt=%sysfunc(datetime());
28
29
30 data _null_;
31 date2=intnx("month",&today.,-1,'end');
32 put date2=;
33 call symputx('DATE2',put(date2 ,date9.));
34 month_id = intck('month','01jan1990'd,today());
35 put month_id=;
36 call symputx('month_id',month_id);
37 run;
date2=21945
month_id=361
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.01 seconds
memory 337.43k
OS Memory 21664.00k
Timestamp 07/02/2020 10:44:40 AM
Step Count 10 Switch Count 0
Page Faults 0
Page Reclaims 50
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0
2 The SAS System 10:12 Friday, February 7, 2020
38
39 %put &=DATE2;
DATE2=31JAN2020
40 %put &=month_id;
MONTH_ID=361
41
42
43 /*STEP 1 */
44
45 proc sql;
46 drop table IRM.TESTPAYM;
NOTE: Table IRM.TESTPAYM has been dropped.
47 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 3.25 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 135.18k
OS Memory 21664.00k
Timestamp 07/02/2020 10:44:44 AM
Step Count 11 Switch Count 2
Page Faults 0
Page Reclaims 17
Page Swaps 0
Voluntary Context Switches 35
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 8
48
49 Proc SQL;
50 connect to teradata
51 (user=&teradata_user. password=&teradata_pwd. server = 'edwprod' database = 'nuc_pl_user_view');
52 Create table IRM.TESTPAYM as select * from connection to teradata(
53
54 select a.ID, a.maxmargin_dt, b.maxmodelmart_d, c.maxexploit_d from
55 (
56 select
57 1 as ID,
58 max(month_end_dt) as maxmargin_dt
59 from nuc_pl_user_view.pm_margin_stack) as A
60 left join (
61 select
62 1 as ID,
63 max(month_id) as maxmodelmart_d
64 from Insights_rm.Consumer_Model_Mart
65 WHERE internal_CA_model_id IN (202,203,204,205,210,220,217,216,234,235,219,223,225,226,228,229,232)) as B
66 on a.ID = b.ID
67 left join (
68 select
69 1 as ID,
70 max(month_end_dt) as maxexploit_d
71 from PROFITABILITY_SANDBOX.exploitation_layer_pm ) as C
72 on a.ID = c.ID
73
74 );
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
3 The SAS System 10:12 Friday, February 7, 2020
NOTE: Table IRM.TESTPAYM created, with 1 rows and 4 columns.
75 disconnect from teradata ;
76 QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 1:03.97
user cpu time 0.05 seconds
system cpu time 0.01 seconds
memory 5369.25k
OS Memory 26788.00k
Timestamp 07/02/2020 10:45:48 AM
Step Count 12 Switch Count 2
Page Faults 0
Page Reclaims 119
Page Swaps 0
Voluntary Context Switches 41
Involuntary Context Switches 1
Block Input Operations 0
Block Output Operations 16
77
78 /* STEP 2 */
79
80 proc sql;
81 drop table IRM.TESTPAYM2;
NOTE: Table IRM.TESTPAYM2 has been dropped.
82 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 3.03 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 134.18k
OS Memory 21920.00k
Timestamp 07/02/2020 10:45:51 AM
Step Count 13 Switch Count 2
Page Faults 0
Page Reclaims 16
Page Swaps 0
Voluntary Context Switches 14
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0
83
84 DATA IRM.TESTPAYM2;
85 SET IRM.TESTPAYM;
86 IF ((maxmargin_dt >=(&date2.)) and
NOTE: Line generated by the macro variable "DATE2".
86 31JAN2020
_______
22
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT, IN,
LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, ^=, |, ||, ~=.
87 (maxmodelmart_d ^= (&month_id.))and
88 (maxexploit_d >=(&date2.)) )
4 The SAS System 10:12 Friday, February 7, 2020
NOTE: Line generated by the macro variable "DATE2".
88 31JAN2020
_______
22
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT, IN,
LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, ^=, |, ||, ~=.
89 then export_flag='Y';
90 else export_flag='N';
91
92 call symputx('export_flag', export_flag);
93
94 run;
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set IRM.TESTPAYM2 may be incomplete. When this step was stopped there were 0 observations and 6 variables.
ERROR: ROLLBACK issued due to errors for data set IRM.TESTPAYM2.DATA.
NOTE: DATA statement used (Total process time):
real time 3.21 seconds
user cpu time 0.05 seconds
system cpu time 0.00 seconds
memory 491.43k
OS Memory 21920.00k
Timestamp 07/02/2020 10:45:54 AM
Step Count 14 Switch Count 2
Page Faults 0
Page Reclaims 41
Page Swaps 0
Voluntary Context Switches 37
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0
WARNING: Apparent symbolic reference EXPORT_FLAG not resolved.
95
96 *check value of macro variable;
97 %PUT Export_Flag = &export_flag;
Export_Flag = &export_flag
98
99 GOPTIONS NOACCESSIBLE;
100 %LET _CLIENTTASKLABEL=;
101 %LET _CLIENTPROCESSFLOWNAME=;
102 %LET _CLIENTPROJECTPATH=;
103 %LET _CLIENTPROJECTNAME=;
104 %LET _SASPROGRAMFILE=;
105
106 ;*';*";*/;quit;run;
107 ODS _ALL_ CLOSE;
108
109
110 QUIT; RUN;
111
