topic Re: Proc export with if condition in SAS Enterprise Guide

Proc export with if condition

FRAFLUTE — Mon, 15 Jul 2019 16:24:24 GMT

Good Morning

with a proc sql I produce the db out_invoices that i want to export only if it is the second monday of the month (a monday in a day between 8 and 14 )

This is the macro that i've created:

%macro export_conditionally;
%if (day(now) between 8 and 14) and workday(now)=2 %then %do;
proc export 
  data=work.out_invoices
  dbms=xlsx 
  outfile="\\23- REPORT\CUSTOMER.xlsx" 
  replace;
run;
%end

%mend;
%export_conditionally;

When i open SAS, in the first time, the program execute correctly the code. From the second run the macro crash and give this error:

_________________________________

NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space

between a quoted string and the succeeding identifier is recommended.

what i'm doing wrong? 😞

Re: Proc export with if condition

tsap — Mon, 15 Jul 2019 16:29:08 GMT

Can you provide the full log pertaining to the code that is running and producing the error as well as the details around it?

Additionally... Can you provide the coding logic around where you actually call this macro?

These details would give us a better idea of what may be happening and causing the error that you see.

Thanks.

Re: Proc export with if condition

Reeza — Mon, 15 Jul 2019 16:35:01 GMT

You cannot use SAS functions inline just like that, you need to wrap them in %SYSFUNC().

%if (day(now) between 8 and 14) and workday(now)=2 %then %do;

What is now?????

Assuming you meant something like today or the date() function something like this should work:

%if  %sysfunc(today(), day.) >=8 and %sysfunc(today(), day.) <=14 %then %do;

@FRAFLUTE wrote:

Good Morning

with a proc sql I produce the db out_invoices that i want to export only if it is the second monday of the month (a monday in a day between 8 and 14 )

This is the macro that i've created:
%macro export_conditionally;
%if (day(now) between 8 and 14) and workday(now)=2 %then %do;
proc export 
  data=work.out_invoices
  dbms=xlsx 
  outfile="\\23- REPORT\CUSTOMER.xlsx" 
  replace;
run;
%end

%mend;
%export_conditionally;
When i open SAS, in the first time, the program execute correctly the code. From the second run the macro crash and give this error:

_________________________________

49

NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space

between a quoted string and the succeeding identifier is recommended.

what i'm doing wrong? 😞

Re: Proc export with if condition

Tom — Mon, 15 Jul 2019 16:36:30 GMT

What exactly are you trying to test in your %IF statement?

Right now you seem be doing a test in form of A and B. Where B is the number 14 which SAS will evaluate as TRUE and A is the string

day(now) between 8

which should cause SAS to generate an error since that is not a valid number.

Do you want to see if today is a monday?

2=%sysfunc(weekday(%sysfunc(today())))

Also you want to see if today's day of the month is between 8 and 14?

8<=%sysfunc(day(%sysfunc(today())))
and %sysfunc(day(%sysfunc(today())))<=14

Re: Proc export with if condition

ballardw — Mon, 15 Jul 2019 16:44:10 GMT

@FRAFLUTE wrote:

Good Morning

with a proc sql I produce the db out_invoices that i want to export only if it is the second monday of the month (a monday in a day between 8 and 14 )

This is the macro that i've created:
%macro export_conditionally;
%if (day(now) between 8 and 14) and workday(now)=2 %then %do;
proc export 
  data=work.out_invoices
  dbms=xlsx 
  outfile="\\23- REPORT\CUSTOMER.xlsx" 
  replace;
run;
%end

%mend;
%export_conditionally;
When i open SAS, in the first time, the program execute correctly the code. From the second run the macro crash and give this error:

_________________________________

49

NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space

between a quoted string and the succeeding identifier is recommended.

what i'm doing wrong? 😞

If what is the second Monday? The date the program runs? The date of the invoices?

Your shown definition of a the macro is missing a semicolon between the %end and %mend so that might be an issue.

Show the entire procedure, data step or macro that generates the warning.

If using a macro make sure the option MPRINT is on. Post the log into a code box opened with the forum's {I} or "running man" icon to preserve the formatting of the error message.

Note that is NOT and error is just says that somewhere, in code you have not shown you likely have some value in quote followed by a letter without a space such as "something"q. Since SAS uses several quoted strings followed by a letter to denote special literal values: date "01JAN2019"d , time "13:30:25"t , datetime "01JAN2019:13:30:25"dt, name literals for variables "Not normal varname"n and such, SAS is warning you that such code might cause an error in the future because the particular quote+character combination you used might mean something special.

Re: Proc export with if condition

Reeza — Mon, 15 Jul 2019 16:49:56 GMT

You should be using NWKDOM to find the second Tuesday and then checking if the date matches that date instead. Sorry, I fixed your code, but didn't check to see if it actually did what you wanted, not sure it does. The NWKDOM appraoch will work though.

Re: Proc export with if condition

FRAFLUTE — Mon, 15 Jul 2019 17:07:16 GMT

This is the full code:

DATA _NULL_;
DATA_R=intnx('month',TODAY(),-1,'beginning');
DATA_u=Intnx('month',TODAY(),-1,'end');
DATA_F=COMPRESS(PUT(DATA_R, DATE9.))!!'_'!!TRIM(PUT(DATA_u, DATE9.))!!"_FT";
DATA_A=COMPRESS(PUT(DATA_R, DATE9.))!!'_'!!TRIM(PUT(DATA_u, DATE9.))!!"_AMM";
CALL SYMPUT('TABELLA',DATA_f);	
CALL SYMPUT('AMM',DATA_A);
CALL SYMPUT('DATA_I',DATA_R);
CALL SYMPUT('DATA_U',DATA_U);
FORMAT DATA_R DATA_u DATE9.;
RUN;
%PUT &TABELLA &AMM &DATA_I &DATA_U;
PROC SQL;
CREATE TABLE _&TABELLA AS
SELECT *
FROM WORK.OUT_INVOICES
WHERE Cod_Cliente=104466 AND DATA_FATTURA BETWEEN &DATA_I AND &DATA_U AND Registro_Iva not='V4';
quit;
proc sql;
CREATE TABLE _&AMM AS
SELECT *
FROM WORK.OUT_INVOICES
WHERE Cod_Cliente=104466 AND DATA_FATTURA BETWEEN &DATA_I AND &DATA_U AND Registro_Iva='V4';
QUIT;


%macro export_conditionally;
%if (day(now) between 8 and 14) and workday(now)=2 %then %do;
proc export 
  data=_&TABELLA
  dbms=xlsx 
  outfile="C:\\23- REPORT\FLEET ADV\FLUSSO\EURO\_&TABELLA - EURO.xlsx" 
  replace;
run;
proc export 
  data=_&AMM
  dbms=xlsx 
   outfile="C:\\23- REPORT\FLEET ADV\FLUSSO\EURO\_&AMM - EURO.xlsx" 
  replace;
run;
%end

%mend;
%export_conditionally;

In the if statement i want to export this two file only when is the second monday of the month.

Thank's to all!

Re: Proc export with if condition

Reeza — Mon, 15 Jul 2019 18:06:55 GMT

1. Use NWKDOM to find the date of the second Tuesday
2. Check if that date and today's date are the same.

I don't see anything about that in your code. So not sure why you're posting it all again.

Re: Proc export with if condition

FRAFLUTE — Tue, 23 Jul 2019 09:43:05 GMT

Thank's for the support!

Below the modified code with the NWKDOM function... but don't run 😞

What could be the problem?

%macro export_conditionally;
%if %sysfunc(today())=%sysfunc(NWKDOM(2,2,month(today()),year(today()))) %then %do;
proc export 
  data=_&TABELLA
  dbms=xlsx 
  outfile="\23- REPORT\FLEET ADV\FLUSSO RAC\EUROPCAR\_&TABELLA - EUROPCAR.xlsx" 
  replace;
run;
proc export 
  data=_&AMM
  dbms=xlsx 
  outfile="\23- REPORT\FLEET ADV\FLUSSO RAC\EUROPCAR\_&AMM - EUROPCAR.xlsx" 
  replace;
run;
%end

%mend;
%export_conditionally;

Re: Proc export with if condition

Astounding — Tue, 16 Jul 2019 12:02:28 GMT

As others have noted, you have logic errors that should be fixed.

However, the first item to fix is the missing semicolon that belongs here:

%end;

Re: Proc export with if condition

Tom — Tue, 16 Jul 2019 12:58:05 GMT

The NWKDOM function is not going to know how to deal with the strings MONTH(TODAY()) and YEAR(TODAY()).

If you want to call a SAS function in macro code then each function needs to enclosed in its own call to the %SYSFUNC() macro function.

%macro export_conditionally;
%local today;
%let today=%sysfunc(today());
%if  &today=%sysfunc(NWKDOM(2,2,%sysfunc(month(&today)),%sysfunc(year(&today)))) %then %do;
proc export 
  data=_&TABELLA
  dbms=xlsx 
  outfile="\\FSITMIVF0230\VLCR$\23- REPORT\FLEET ADV\FLUSSO RAC\EUROPCAR\_&TABELLA - EUROPCAR.xlsx" 
  replace;
run;
proc export 
  data=_&AMM
  dbms=xlsx 
  outfile="\\FSITMIVF0230\VLCR$\23- REPORT\FLEET ADV\FLUSSO RAC\EUROPCAR\_&AMM - EUROPCAR.xlsx" 
  replace;
run;
%end;

%mend;

Re: Proc export with if condition

Astounding — Tue, 16 Jul 2019 13:17:27 GMT

What Tom says about needing %sysfunc for every nonmacro function is correct. In context you might choose a different path, however. Your full program includes a DATA _NULL_ step earlier. That step could use the same functions with zero use of %sysfunc, and generate a macro variable using CALL SYMPUTX. For example a macro variable Second_monday could be assigned a value of Y or N in that DATA step.