If then and proc export

[jorquec]

 Hi all, 

Please how do I make this work ? I would like to make sure to execute the proc export only if both conditions are true , if not then do nothing.

DATA JORQUEC.TESTz;

SET JORQUEC.TEST;

IF  maxmargin_dt = 30/06/2019 and   maxmodelmart_d =359 then do;

end;

 PROC EXPORT DATA=JORQUEC.TEST

 OUTFILE='//SASCommon/jorquec/TEST3.CSV'

 DBMS=dlm 

 REPLACE;

  delimiter='&';

  RUN;

[andreas_lds]

You should know that maxmargin_dt = 30/06/2019 is the same as maxmargin_dt = 0,00247647350173353145121347201585, so the first thing to do is writing a proper filter.

The filter can then be integrated into proc export, please note that i assumed that maxmargin_dt is a date variable, if not errors will appear:

proc export data=jorquec.test(where=(maxmargin_dt = '30Jun2019'd and maxmodelmart_d = 359))
      outfile='//SASCommon/jorquec/TEST3.csv'
      dbms=dlm 
      replace;
   delimiter='&';
run;

[jorquec]

Hi, 

thanks for your collaboration, I repalace with my macro data  however now it doesn't work I received a message  Error 22-7 "Invalid option name And"

It seems he doesn't recognize the comand "and" inside where ()

proc export data=jorquec.test(where=(maxmargin_dt = &ONEDT2.) and (maxmodelmart_d is <> &month_id.))
outfile='//SASCommon/jorquec/TEST1.csv'
dbms=dlm
replace;
delimiter='&';
run;

[Kurt_Bremser]

You have extraneous parentheses, remove them:

proc export
  data=jorquec.test (
    where=(maxmargin_dt = &ONEDT2. and maxmodelmart_d is <> &month_id.)
  )
  outfile='//SASCommon/jorquec/TEST1.csv'
  dbms=dlm
  replace
;

As you can see, a little visual formatting goes a long way in making code more readable.

[jorquec]

  Thanks but I still have more errors 

     proc export

data=jorquec.test(

where=( maxmargin_dt = '30Jun2019'd and maxmodelmart_d is =359)

)

outfile='//SASCommon/jorquec/TEST1.csv'
dbms=dlm
replace;
delimiter='&';
run;
       LOG File errors:                                                           

ERROR 22-322: Syntax error, expecting one of the following: MISSING, NOT, NULL, ^, ~.  

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

36               outfile='//SASCommon/jorquec/TEST1.csv'

ERROR: Syntax error while parsing WHERE clause.

[andreas_lds]

Please use the online documentation to find the appropriate comparison operators. Using the docs is almost always faster then asking the community. You can find the doc there: https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=pgmsashome&docsetTarget=h...

[jorquec]

 Hi

I have already read help documentation the problem is that this  where condition is not working properly as condition , I mean SAS is running proc export even the condition is not true .  So please could someone really help me ?

Many thanks,

This are the results in my data jorquec.test

ID  maxmargin_dt maxmodelmart_d

1 30JUN2019 359

So I wrote the code below using maxmodelmart_d = 360 , I expected that prox export does't create a file because one of the conditions is false however he created a file  test2.csv !!!

----SAS  CODE ---

proc export 

data=jorquec.test(

where=( maxmargin_dt = '30Jun2019'd and maxmodelmart_d =360)

)

      outfile='//SASCommon/jorquec/TEST2.csv'

      dbms=dlm 

      replace;

   delimiter='&';

run;

[andreas_lds]

Unfortunately you still spend a close to zero commitment in writing your own posts:

• code is not posted using the appropriate function => click on the running man icon, post code in the box
• code is not formatted
• data is not posted in usable form

But I should have paid more attention while reading your first post. What you want can't be achieved by using the where-option, sorry for the inconvenience. With the where-option you can only select the observations to be exported not prevent the export of an empty file.

What you actually need is a data-null-step with call execute:

data _null_;
  set jorquec.test(
    where=(maxmargin_dt = '30Jun2019'd and maxmodelmart_d =360)
  );

  call execute(catx(" ", "proc export data=jorquec.test outfile='//SASCommon/jorquec/TEST3.CSV'",
    "dbms=dlm replace; delimiter='&'; run;'));
run;

[jorquec]

Sorry, 

I didn' t understand I used" if then do as" my first option, then you suggested me to use "where " , please don't be critical .

I am new on SAS, English is not but mother language so just be patient .

Someone could please help me ?

I just want a code with If then do that just do the Proc Export if all conditions are true. 

DATA JORQUEC.TEST2;

SET JORQUEC.TEST;

IF  (maxmargin_dt = &ONEDT2.) and (maxmodelmart_d <> &month_id.) then do;  

end;

 PROC EXPORT DATA=JORQUEC.TEST

 OUTFILE='//SASCommon/jorquec/TRIGGER_PAYGO.CSV'

 DBMS=dlm 

 REPLACE;

  delimiter='&';

 RUN;

[Reeza]

We don't understand the condition. Does your data set have only one row? Your If condition would evaluate for multiple lines of your data so it's possible that some will be true and some will be false? That's kinda confusing. In general, you cannot run conditional code like that within data steps, you cannot nest data steps and procs in that manner and you really don't want to. 

What version of SAS do you have? The latest has some new features that will make this easier, older versions may be harder. This will involve macro language but it looks like you're already working with that. Is this block of code within a macro already? How much do you know about macros?

Is there a reason you have a file with an extension of CSV with & as the delimiter? That's not a CSV file, CSV are separated by either comma's or semicolons (French).  CSV = Comma Separated Values. 

If you can answer the questions (I've bolded them for you) I'll be happy to help you out. 

[jorquec]

Many thanks for your  help.

These are my answers:

Does your data set have only one row?  Yes just one row  as below:

my dataset called JORQUEC.TEST has just one row with 3 variables:

ID   maxmargin_dt   maxmodelmart_d

1       30JUN2019       359

Your If condition would evaluate for multiple lines of your data so it's possible that some will be true and some will be false? 

No it will evaluate just one line.

What version of SAS do you have?  9.4

Is this block of code within a macro already? yes

How much do you know about macros? almost nothing I am struggling with this .

Is there a reason you have a file with an extension of CSV with & as the delimiter?  No it could be any kind of file .

Let me clarify better my objective:

Step1: I check two diferent  tables and extract the maximum date from those, with this information save a data set Jorquec.test

Step2: 

Step3: ( most difficult for me) I need to check if those dates maxmargin_dt is equal to previous month ( a macro month that I called ONEDT2  , I really dont know if it is correct ) and maxmodelmart_d is different from ( a macro called monthid), if both conditions are true then create a file that could be an empty file as well as I just need to create this file because this would be my trigger for another process.

Does it make sense now for you?

/*-----------------SAS  CODE ------------------*/

%let today=%sysfunc(today());

%let currdt=%sysfunc(datetime());

%let month_id = %str(%')&MONTHID1.%str(%');/*'340';*/

data _null_;

date2=intnx("month",&today.,-1,'end');

call symput('ONEDT2',"1"||substr(put(date2,DDMMYYN.),7,2)||substr(put(date2,DDMMYYN.),3,2)||substr(put(date2,DDMMYYN.),1,2));

%put &ONEDT2.;

mthid1=intck('month','01jan1990'd,&today.)+1-1;

call symput('MONTHID1',put(mthid1,3.));

%put &MONTHID1.;

run;

/*STEP 1 */

Proc SQL;

connect to teradata 

(user=&teradata_user. password=&teradata_pwd. server = 'edwprod' database = 'nuc_pl_user_view');  

Create  table JORQUEC.TEST 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 */

DATA JORQUEC.TEST2;

SET JORQUEC.TEST;

IF  (maxmargin_dt = &ONEDT2.) and (maxmodelmart_d <> &month_id.) then do;  

end;

/* STEP 3 - PROC EXPORT -*/

 PROC EXPORT DATA=JORQUEC.TEST

 OUTFILE='//SASCommon/jorquec/TRIGGER_PAYGO.CSV'

 DBMS=dlm 

 REPLACE;

  delimiter='&';

 RUN;

[Reeza]

1. create a macro variable that indicates if your condition is met (orange)

2. Use an %IF/%THEN loop to execute your export. (purple)

I do not know if your condition works, I would be testing that first and making sure that the macro variable is created correctly using a PUT statement (red below). Once you have it working, you can delete or comment out that section.

DATA JORQUEC.TEST2;
SET JORQUEC.TEST;
IF  (maxmargin_dt = &ONEDT2.) 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 -*/
 
%if (&export_flag = Y) %then %do;

 PROC EXPORT DATA=JORQUEC.TEST
 OUTFILE='//SASCommon/jorquec/TRIGGER_PAYGO.TXT'
 DBMS=dlm 
 REPLACE;
  delimiter='&';
 RUN;

 %end;


 

---

@jorquec wrote:

Many thanks for your  help.

These are my answers:

Does your data set have only one row?  Yes just one row  as below:

my dataset called JORQUEC.TEST has just one row with 3 variables:

ID   maxmargin_dt   maxmodelmart_d

1       30JUN2019       359

Your If condition would evaluate for multiple lines of your data so it's possible that some will be true and some will be false? 

No it will evaluate just one line.

What version of SAS do you have?  9.4

Is this block of code within a macro already? yes

How much do you know about macros? almost nothing I am struggling with this .

Is there a reason you have a file with an extension of CSV with & as the delimiter?  No it could be any kind of file .

Let me clarify better my objective:

Step1: I check two diferent  tables and extract the maximum date from those, with this information save a data set Jorquec.test

Step2: 

Step3: ( most difficult for me) I need to check if those dates maxmargin_dt is equal to previous month ( a macro month that I called ONEDT2  , I really dont know if it is correct ) and maxmodelmart_d is different from ( a macro called monthid), if both conditions are true then create a file that could be an empty file as well as I just need to create this file because this would be my trigger for another process.

Does it make sense now for you?

/*-----------------SAS  CODE ------------------*/

%let today=%sysfunc(today());

%let currdt=%sysfunc(datetime());

%let month_id = %str(%')&MONTHID1.%str(%');/*'340';*/

data _null_;

date2=intnx("month",&today.,-1,'end');

call symput('ONEDT2',"1"||substr(put(date2,DDMMYYN.),7,2)||substr(put(date2,DDMMYYN.),3,2)||substr(put(date2,DDMMYYN.),1,2));

%put &ONEDT2.;

mthid1=intck('month','01jan1990'd,&today.)+1-1;

call symput('MONTHID1',put(mthid1,3.));

%put &MONTHID1.;

run;

/*STEP 1 */

Proc SQL;

connect to teradata 

(user=&teradata_user. password=&teradata_pwd. server = 'edwprod' database = 'nuc_pl_user_view');  

Create  table JORQUEC.TEST 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 */

DATA JORQUEC.TEST2;

SET JORQUEC.TEST;

IF  (maxmargin_dt = &ONEDT2.) and (maxmodelmart_d <> &month_id.) then do;  

end;

/* STEP 3 - PROC EXPORT -*/

 PROC EXPORT DATA=JORQUEC.TEST

 OUTFILE='//SASCommon/jorquec/TRIGGER_PAYGO.CSV'

 DBMS=dlm 

 REPLACE;

  delimiter='&';

 RUN;

---

[jorquec]

Thanks many thanks , it is perfect. So glad for your help.

[Tom · Accepted solution]

So you only want to write the text file if will have more than zero observation that meet that condition?

proc sql noprint;
%let anyobs=0;
select '1'  into :anyobs
from SET JORQUEC.TEST
where maxmargin_dt = '30JUN2019'd
  and maxmodelmart_d = 359 
;
quit;

%if (&anyobs) %then %do;
PROC EXPORT
  DATA=JORQUEC.TEST
  OUTFILE='//SASCommon/jorquec/TEST3.CSV' REPLACE
  DBMS=dlm 
;
  delimiter='&';
RUN;
%end;

[jorquec]

Many thanks for your suggestion 

Just a question 

 as example   if  maxmodelmart_d ( which is a macro date, I just wrote as number to be easy )  is different from what was saved on Jorquec.test so the
 if then do will not run ?  Because is exactly this check that I need, if the actual value of the macro is different from what is saved then don run the proc export.