Hi
Please see code and log as I told before about Replace inside STEP 3.
If you could please say how is the correct way to fix it. Some libraries mentioned are used in another steps of my code that are not here.
%include '/SASCommon/jorquec/credentials.sas';
libname sasmf '//SASCommon/ModelFactory/Trigger' ;
libname nuv teradata user= &teradata_user. password = &teradata_pwd. server = 'edwprod' database = 'nuc_pl_user_view';
libname irm teradata user= &teradata_user. password = &teradata_pwd. server = 'edwprod' database = 'INSIGHTS_RM';
/*-----------------MACROS DE DATA ------------------*/
%let today=%sysfunc(today());
%let currdt=%sysfunc(datetime());
data _null_;
date2=intnx("month",&today.,-1,'end');
call symputx('date2',date2);
/*%put &=date2.;*/
/*%put DATE2 = %sysfunc(putn(&date2.,date9.));*/
month_id = intck('month','01jan1990'd,today());
put month_id=;
call symputx('month_id',month_id);
%put month_id=&month_id.;
run;
/*STEP 1 */
proc sql;
drop table IRM.TESTPAYG;
quit;
Proc SQL;
connect to teradata
(user=&teradata_user. password=&teradata_pwd. server = 'edwprod' database = 'nuc_pl_user_view');
Create table IRM.TESTPAYG as select * from connection to teradata(
select a.ID, a.maxmargin_dt, b.maxmodelmart_d from
(
select
1 as ID,
max(month_end_dt) as maxmargin_dt
from nuc_pl_user_view.pg_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 (101,107,110,116,117,119,120)) as B
on a.ID = b.ID
);
disconnect from teradata ;
QUIT;
/* STEP 2 */
proc sql;
drop table IRM.TESTPAYG2;
quit;
DATA IRM.TESTPAYG2;
SET IRM.TESTPAYG;
IF (maxmargin_dt >=(&date2.)) and (maxmodelmart_d ^= (&month_id.)) 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;
/* STEP 3 - PROC EXPORT -*/
%macro test;
%if &export_flag = N %then replace %do;
data sasmf.TRIGGERPAYG;
SET IRM.TESTPAYG2;
run;
%end;
%mend;
%test;
/* ------- LOG FILE for this code below ------- */
1 The SAS System 08:44 Thursday, September 12, 2019
1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='sascomnun';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='D:\EM_TMP_20161021\Consumer_Insights\Individual folders\Claudia\Model_rebuild\2019\Trigger
5 ! Process\Trigger event_PAYG.egp';
6 %LET _CLIENTPROJECTPATHHOST='RESCZKWTS004';
7 %LET _CLIENTPROJECTNAME='Trigger event_PAYG.egp';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=PNG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 FILENAME EGPDF TEMP;
15 ODS PDF(ID=EGPDF) FILE=EGPDF STYLE=Pearl;
NOTE: Writing ODS PDF(EGPDF) output to DISK destination "EGPDF", printer "PDF".
16 FILENAME EGSR TEMP;
17 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
18 STYLE=HtmlBlue
19 STYLESHEET=(URL="file:///C:/SAS94/software/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
20 NOGTITLE
21 NOGFOOTNOTE
22 GPATH=&sasworklocation
23 ENCODING=UTF8
24 options(rolap="on")
25 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
26 FILENAME EGXLSSX TEMP;
27 ODS EXCEL(ID=EGXLSSX) FILE=EGXLSSX STYLE=Excel
28 OPTIONS (
29 EMBEDDED_TITLES="no" EMBEDDED_FOOTNOTES="no"
30 );
31
32 GOPTIONS ACCESSIBLE;
33
34
35 %include '/SASCommon/jorquec/credentials.sas';
42
43 libname sasmf '//SASCommon/ModelFactory/Trigger' ;
NOTE: Libref SASMF was successfully assigned as follows:
Engine: V9
Physical Name: //SASCommon/ModelFactory/Trigger
44 libname nuv teradata user= &teradata_user. password = &teradata_pwd. server = 'edwprod' database = 'nuc_pl_user_view';
NOTE: Libref NUV was successfully assigned as follows:
Engine: TERADATA
Physical Name: edwprod
45 libname irm teradata user= &teradata_user. password = &teradata_pwd. server = 'edwprod' database = 'INSIGHTS_RM';
NOTE: Libref IRM was successfully assigned as follows:
Engine: TERADATA
Physical Name: edwprod
46
47 /*-----------------MACROS DE DATA ------------------*/
48
49 %let today=%sysfunc(today());
50 %let currdt=%sysfunc(datetime());
51
2 The SAS System 08:44 Thursday, September 12, 2019
52 data _null_;
53
54 date2=intnx("month",&today.,-1,'end');
55 call symputx('date2',date2);
56 /*%put &=date2.;*/
57 /*%put DATE2 = %sysfunc(putn(&date2.,date9.));*/
58
59
60
61 month_id = intck('month','01jan1990'd,today());
62 put month_id=;
63 call symputx('month_id',month_id);
64 %put month_id=&month_id.;
month_id=356
65
66 run;
month_id=356
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 344.43k
OS Memory 33740.00k
Timestamp 12/09/2019 09:54:01 AM
Step Count 21 Switch Count 0
Page Faults 0
Page Reclaims 31
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 1
Block Input Operations 0
Block Output Operations 8
67
68 /*STEP 1 */
69 proc sql;
70 drop table IRM.TESTPAYG;
NOTE: Table IRM.TESTPAYG has been dropped.
71 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.61 seconds
user cpu time 0.02 seconds
system cpu time 0.00 seconds
memory 152.46k
OS Memory 33740.00k
Timestamp 12/09/2019 09:54:01 AM
Step Count 22 Switch Count 1
Page Faults 0
Page Reclaims 30
Page Swaps 0
Voluntary Context Switches 16
Involuntary Context Switches 2
Block Input Operations 0
Block Output Operations 0
3 The SAS System 08:44 Thursday, September 12, 2019
72
73 Proc SQL;
74 connect to teradata
75 (user=&teradata_user. password=&teradata_pwd. server = 'edwprod' database = 'nuc_pl_user_view');
76 Create table IRM.TESTPAYG as select * from connection to teradata(
77
78 select a.ID, a.maxmargin_dt, b.maxmodelmart_d from
79 (
80 select
81 1 as ID,
82 max(month_end_dt) as maxmargin_dt
83 from nuc_pl_user_view.pg_margin_stack) as A
84 left join (
85 select
86 1 as ID,
87 max(month_id) as maxmodelmart_d
88 from Insights_rm.Consumer_Model_Mart
89 where internal_CA_model_id in (101,107,110,116,117,119,120)) as B
90 on a.ID = b.ID
91
92
93 );
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
NOTE: Table IRM.TESTPAYG created, with 1 rows and 3 columns.
94 disconnect from teradata ;
95 QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 8.00 seconds
user cpu time 0.06 seconds
system cpu time 0.01 seconds
memory 5340.40k
OS Memory 38864.00k
Timestamp 12/09/2019 09:54:09 AM
Step Count 23 Switch Count 1
Page Faults 0
Page Reclaims 97
Page Swaps 0
Voluntary Context Switches 57
Involuntary Context Switches 8
Block Input Operations 0
Block Output Operations 0
96
97 /* STEP 2 */
98
99 proc sql;
100 drop table IRM.TESTPAYG2;
NOTE: Table IRM.TESTPAYG2 has been dropped.
101 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.21 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 133.21k
OS Memory 33996.00k
4 The SAS System 08:44 Thursday, September 12, 2019
Timestamp 12/09/2019 09:54:10 AM
Step Count 24 Switch Count 1
Page Faults 0
Page Reclaims 18
Page Swaps 0
Voluntary Context Switches 12
Involuntary Context Switches 1
Block Input Operations 0
Block Output Operations 0
102
103 DATA IRM.TESTPAYG2;
104 SET IRM.TESTPAYG;
105 IF (maxmargin_dt >=(&date2.)) and (maxmodelmart_d ^= (&month_id.)) then export_flag='Y';
106
107 else export_flag='N';
108
109 call symputx('export_flag', export_flag);
110
111
112 run;
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
NOTE: There were 1 observations read from the data set IRM.TESTPAYG.
NOTE: The data set IRM.TESTPAYG2 has 1 observations and 4 variables.
NOTE: DATA statement used (Total process time):
real time 0.44 seconds
user cpu time 0.05 seconds
system cpu time 0.00 seconds
memory 536.12k
OS Memory 33996.00k
Timestamp 12/09/2019 09:54:10 AM
Step Count 25 Switch Count 1
Page Faults 0
Page Reclaims 81
Page Swaps 0
Voluntary Context Switches 31
Involuntary Context Switches 7
Block Input Operations 0
Block Output Operations 8
113
114 *check value of macro variable;
115 %PUT Export_Flag = &export_flag;
Export_Flag = N
116
117 /* STEP 3 - PROC EXPORT -*/
118
119 %macro test;
120 %if &export_flag = N %then replace %do;
121
122 data sasmf.TRIGGERPAYG;
123 SET IRM.TESTPAYG2;
124 run;
125
126 %end;
5 The SAS System 08:44 Thursday, September 12, 2019
127 %mend;
128 %test;
NOTE: Line generated by the invoked macro "TEST".
128 replace
_______
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
NOTE: Line generated by the invoked macro "TEST".
128 SET IRM.TESTPAYG2;
___
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
129
130
131
132
133
134
135
136 GOPTIONS NOACCESSIBLE;
137 %LET _CLIENTTASKLABEL=;
138 %LET _CLIENTPROCESSFLOWNAME=;
139 %LET _CLIENTPROJECTPATH=;
140 %LET _CLIENTPROJECTPATHHOST=;
141 %LET _CLIENTPROJECTNAME=;
142 %LET _SASPROGRAMFILE=;
143 %LET _SASPROGRAMFILEHOST=;
144
145 ;*';*";*/;quit;run;
146 ODS _ALL_ CLOSE;
NOTE: ODS PDF(EGPDF) printed no output.
(This sometimes results from failing to place a RUN statement before the ODS PDF(EGPDF) CLOSE statement.)
NOTE: Writing EXCEL(EGXLSSX) file: /saswork94/SAS_work298E00009C21_czhrk-dl580nucprod-310-fe/#LN00335
147
148
149 QUIT; RUN;
150
