Morning
Hi Could someone help me I would like to change this code on STEP 3 ? Instead of a new file I would like to replace the existing file
triggerpayg.
/*-----------------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) 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 = Y %then %do; data sasmf.TRIGGERPAYG; SET IRM.TESTPAYG2; run; %end; %mend; %test;An then I tried to replace by this code , but it is not correct : How do I rewrite this ?
Many thanks,
Claudia
"Isn't correct" is awful vague. What makes you think it is "not correct".
Are there errors in the log?: Post the code and log in a code box opened with the {i} to maintain formatting of error messages.
No output? Post any log in a code box.
Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.
If macros are involved then usually you want to set OPTIONS MPRINT; prior to running the code to generate the log so we can see what actually happens more clearly. And with macro variables not passed as parameters perhaps include SYMBOLGEN and MLOGIC to the options.
I do notice that your test macro references a library that isn't used anywhere else. Are you sure the library is assigned?
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.
Find more tutorials on the SAS Users YouTube channel.