if statement

jorquec — Fri, 07 Feb 2020 10:51:18 GMT

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

Re: if statement

ed_sas_member — Fri, 07 Feb 2020 11:21:09 GMT

Hi @jorquec

You should not apply a format to your macro variable date2.

A format is just for display and not for calculation.

Instead of this

call symputx('DATE2',put(date2 ,date9.));

-> try this:

call symputx('DATE2',date2);

Best,

Re: if statement

ed_sas_member — Fri, 07 Feb 2020 11:46:31 GMT

You could also do this:

%let today    =%sysfunc(today());
%let currdt   =%sysfunc(datetime());
%let date2    = %sysfunc(intnx(month, &today., -1, e));
%let month_id = %sysfunc(intck(month, '01jan1990'd, &today.));

%put &=DATE2;
%put &=month_id;

Re: if statement

Kurt_Bremser — Fri, 07 Feb 2020 12:27:22 GMT

Do NOT use formats on macro variables that you use exclusively in code as values.

call symputx('DATE2',put(date2 ,date9.));

must therefore be

call symputx('DATE2',date2);

See Maxim 28.

topic Re: if statement in New SAS User

if statement

Re: if statement

Re: if statement

Re: if statement