BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Cello23
Quartz | Level 8

hello guys,

Please can you help me?


I would like to export two archives is done only when obs is > 0 for both.


I tried to set the macro below but not working ....


THANKS!


data _null_;
if nobs>0 then call symput("r02",1);
else  call symput ("r02",0);
set WORKUSI.empty nobs=nobs;
run;


data _null_;
if nobs>0 then call symput("r01",1);
else  call symput ("r01", 0);
set WORKUSI.full nobs=nobs;
run;

%macro export;
%if (&r01 = 1 and &r02 = 1 )%then %do;
PROC EXPORT DATA=WORKUSI.empty
  OUTFILE= "c:\report.xls"
  DBMS=EXCEL REPLACE;
  SHEET="empty";
  run;
PROC EXPORT DATA=WORKUSI.full
  OUTFILE= "c:\report.xls"
  DBMS=EXCEL REPLACE;
  SHEET="full";
  run;
%end;
%else %do;
filename mymail email to='test@test.it'
subject="KO";
data _null_;
file mymail;
run;
%end;
%mend export;

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

Instead of

210  file mymail;

211  run;

212  %end;

213  %mend %export;

What Tom means is:

....

%mend;

%export

View solution in original post

8 REPLIES 8
Haikuo
Onyx | Level 15

More details would help. Not working how? Please turn on

options mprint;

options mlogic;

options symbolgen;

and then post your log.

Haikuo

Tom
Super User Tom
Super User

The code in the %ELSE %DO part is just part of a data step.  You need to at least add the DATA _NULL_ line.  You also might want to include a PUT statement so that does generate a totally empty mail message.

You can also make it run faster if the files are large by adding code to the two datasteps that are setting the macro variables.  SAS does not need to read through the whole dataset to set the NOBS variable.  For example you could add a STOP statement before the SET statement.

Cello23
Quartz | Level 8

Sorry ....

The proc export  should work only if the two datasets are not empty, if one or both Are empty should send an email.

With the listing below, the proc export end the mail does not work (the log is down)

if nobs>0 then call symput("r02",1);
else  call symput ("r02",0);
set WORKUSI.Pieno_2 nobs=nobs;
run;
data _null_;
if nobs>0 then call symput("r01",1);
else  call symput ("r01", 0);
set WORKUSI.pieno nobs=nobs;
run;

%macro export;
%if (&r01 = 1 and &r02 = 1 )%then %do;
PROC EXPORT DATA=WORKUSI.Pieno_2
  OUTFILE= "\\C0nasc00.intranet.unicredit.it\c0bdm$\banca_diretta_multicanale\TERMINALI REMOTI\STATISTICHE\ATM\POPUP\ESITI AUTO\sas\pippo.xls"
  DBMS=EXCEL REPLACE;
  SHEET="Pieno_2";
  run;
PROC EXPORT DATA=WORKUSI.pieno
  OUTFILE= "\\C0nasc00.intranet.unicredit.it\c0bdm$\banca_diretta_multicanale\TERMINALI REMOTI\STATISTICHE\ATM\POPUP\ESITI AUTO\sas\pippo.xls"
  DBMS=EXCEL REPLACE;
  SHEET="pieno";
  run;
%end;
%else %do;
filename mymail email to='prova@prova.it'
subject="KO: Estrazione automatica esiti popup.";
data _null_;
file mymail;
run;
%end;
%mend export;  

Sorry but the log is in italian:

7070
7071  data _null_;
7072  if nobs>0 then call symput("r02",1);
7073  else  call symput ("r02",0);
7074  set WORKUSI.Pieno_2 nobs=nobs;
7075  run;

NOTA: I valori numerici sono stati convertiti in valori
      alfanumerici alle posizioni date da: (Riga):(Colonna).
      7072:34   7073:26
NOTA: Sono state lette 14 osservazioni dal data set WORKUSI.PIENO_2.
NOTA: L'istruzione DATA ha utilizzato (tempo totale di elaborazione):
      tempo reale         0.37 secondi
      tempo cpu           0.00 secondi


7076  data _null_;
7077  if nobs>0 then call symput("r01",1);
7078  else  call symput ("r01", 0);
7079  set WORKUSI.pieno nobs=nobs;
7080  run;

NOTA: I valori numerici sono stati convertiti in valori
      alfanumerici alle posizioni date da: (Riga):(Colonna).
      7077:34   7078:27
NOTA: Sono state lette 14 osservazioni dal data set WORKUSI.PIENO.
NOTA: L'istruzione DATA ha utilizzato (tempo totale di elaborazione):
      tempo reale         0.32 secondi
      tempo cpu           0.03 secondi


7081
7082  %macro export;
7083  %if (&r01 = 1 and &r02 = 1 )%then %do;
7084  PROC EXPORT DATA=WORKUSI.Pieno_2
7085          OUTFILE=
7085! "\\C0nasc00.intranet.unicredit.it\c0bdm$\banca_diretta_multicanale\TERMINALI
7085! REMOTI\STATISTICHE\ATM\POPUP\ESITI AUTO\sas\pippo.xls"
7086          DBMS=EXCEL REPLACE;
7087          SHEET="Pieno_2";
7088          run;
7089  PROC EXPORT DATA=WORKUSI.pieno
7090          OUTFILE=
7090! "\\C0nasc00.intranet.unicredit.it\c0bdm$\banca_diretta_multicanale\TERMINALI
7090! REMOTI\STATISTICHE\ATM\POPUP\ESITI AUTO\sas\pippo.xls"
7091          DBMS=EXCEL REPLACE;
7092          SHEET="pieno";
7093          run;
7094  %end;
7095  %else %do;
7096  filename mymail email to='prova@prova.it'
7097  subject="KO: Estrazione automatica esiti popup.";
7098  data _null_;
7099  file mymail;
7100  run;
7101  %end;
7102  %mend export;

Tom
Super User Tom
Super User

Unless you truncated the log it does not look like you ever called the macro that you defined.

Add %export; to the end of the program so that it will execute.

Cello23
Quartz | Level 8

Sorry Tom, i added %export, this is the log:

187  data _null_;
188  if nobs>0 then call symput("r01",1);
189  else  call symput ("r01", 0);
190  set WORKUSI.pieno nobs=nobs;
191  run;

NOTA: I valori numerici sono stati convertiti in valori alfanumerici alle posizioni date da:
      (Riga):(Colonna).
      188:34   189:27
NOTA: Sono state lette 14 osservazioni dal data set WORKUSI.PIENO.
NOTA: L'istruzione DATA ha utilizzato (tempo totale di elaborazione):
      tempo reale         0.18 secondi
      tempo cpu           0.00 secondi


192
193  %macro export;
194  %if (&r01 = 1 and &r02 = 1 )%then %do;
195  PROC EXPORT DATA=WORKUSI.Pieno_2
196          OUTFILE= "\\C0nasc00.intranet.unicredit.it\c0bdm$\banca_diretta_multicanale\TERMINALI
196!  REMOTI\STATISTICHE\ATM\POPUP\ESITI AUTO\sas\pippo.xls"
197          DBMS=EXCEL REPLACE;
198          SHEET="Pieno_2";
199          run;
200  PROC EXPORT DATA=WORKUSI.pieno
201          OUTFILE= "\\C0nasc00.intranet.unicredit.it\c0bdm$\banca_diretta_multicanale\TERMINALI
201!  REMOTI\STATISTICHE\ATM\POPUP\ESITI AUTO\sas\pippo.xls"
202          DBMS=EXCEL REPLACE;
203          SHEET="pieno";
204          run;
205  %end;
206  %else %do;
207  filename mymail email to='prova@prova.it'
208  subject="KO: Estrazione automatica esiti popup.";
209  data _null_;
210  file mymail;
211  run;
212  %end;
213  %mend %export;
AVVERTIMENTO: Ignorate le informazioni estranee nell'istruzione %MEND per la definizione macro
              EXPORT.


"WARNING: Ignore the extraneous information in the% MEND define macros for EXPORT."

Haikuo
Onyx | Level 15

Instead of

210  file mymail;

211  run;

212  %end;

213  %mend %export;

What Tom means is:

....

%mend;

%export

Tom
Super User Tom
Super User

Better syntax would be to leave the macro name in the %MEND statement.

...

%mend export;

%export ;

In addition to making it easier on humans to read your code it can help trap mistakes.  So if you make a mistake that causes the %MACRO and %MEND statements to get mis-matched SAS will issue the warning message about extraneous information in the %MEND statement.

Cello23
Quartz | Level 8

Thank's !!!

Smiley Wink

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1434 views
  • 6 likes
  • 3 in conversation