DATA Step, Macro, Functions and more

Macro export - help please ....

Accepted Solution Solved
Reply
Contributor
Posts: 51
Accepted Solution

Macro export - help please ....

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;


Accepted Solutions
Solution
‎03-16-2012 12:16 PM
Respected Advisor
Posts: 3,156

Re: Macro export - help please ....

Instead of

210  file mymail;

211  run;

212  %end;

213  %mend %export;

What Tom means is:

....

%mend;

%export

View solution in original post


All Replies
Respected Advisor
Posts: 3,156

Macro export - help please ....

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

options mprint;

options mlogic;

options symbolgen;

and then post your log.

Haikuo

Super User
Super User
Posts: 7,060

Macro export - help please ....

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.

Contributor
Posts: 51

Re: Macro export - help please ....

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;

Super User
Super User
Posts: 7,060

Re: Macro export - help please ....

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.

Contributor
Posts: 51

Re: Macro export - help please ....

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."

Solution
‎03-16-2012 12:16 PM
Respected Advisor
Posts: 3,156

Re: Macro export - help please ....

Instead of

210  file mymail;

211  run;

212  %end;

213  %mend %export;

What Tom means is:

....

%mend;

%export

Super User
Super User
Posts: 7,060

Re: Macro export - help please ....

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.

Contributor
Posts: 51

Re: Macro export - help please ....

Thank's !!!

Smiley Wink

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

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