topic Re: If then and proc export in New SAS User

If then and proc export

jorquec — Fri, 12 Jul 2019 07:42:42 GMT

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;

Re: If then and proc export

andreas_lds — Fri, 12 Jul 2019 08:25:09 GMT

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;

Re: If then and proc export

jorquec — Fri, 12 Jul 2019 08:40:01 GMT

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;

Re: If then and proc export

Kurt_Bremser — Fri, 12 Jul 2019 08:53:08 GMT

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.

Re: If then and proc export

jorquec — Fri, 12 Jul 2019 09:07:55 GMT

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.

Re: If then and proc export

andreas_lds — Fri, 12 Jul 2019 09:12:18 GMT

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=home.htm

Re: If then and proc export

jorquec — Fri, 12 Jul 2019 09:50:27 GMT

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;

Re: If then and proc export

andreas_lds — Fri, 12 Jul 2019 11:18:33 GMT

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;

Re: If then and proc export

jorquec — Fri, 12 Jul 2019 11:51:09 GMT

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;

Re: If then and proc export

Reeza — Fri, 12 Jul 2019 15:12:06 GMT

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.

Re: If then and proc export

Tom — Fri, 12 Jul 2019 15:53:03 GMT

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;

Re: If then and proc export

jorquec — Fri, 12 Jul 2019 16:27:36 GMT

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;

Re: If then and proc export

jorquec — Fri, 12 Jul 2019 16:36:46 GMT

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.

Re: If then and proc export

Reeza — Fri, 12 Jul 2019 16:54:24 GMT

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;

Re: If then and proc export

jorquec — Sat, 13 Jul 2019 16:45:51 GMT

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