topic Re: Macro export - help please .... in SAS Programming

Macro export - help please ....

Cello23 — Fri, 16 Mar 2012 10:45:52 GMT

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;

Macro export - help please ....

Haikuo — Fri, 16 Mar 2012 12:09:43 GMT

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

options mprint;

options mlogic;

options symbolgen;

and then post your log.

Haikuo

Macro export - help please ....

Tom — Fri, 16 Mar 2012 12:45:58 GMT

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.

Re: Macro export - help please ....

Cello23 — Fri, 16 Mar 2012 14:46:48 GMT

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;

Re: Macro export - help please ....

Tom — Fri, 16 Mar 2012 15:01:44 GMT

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.

Re: Macro export - help please ....

Cello23 — Fri, 16 Mar 2012 16:08:33 GMT

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

Re: Macro export - help please ....

Haikuo — Fri, 16 Mar 2012 16:16:11 GMT

Instead of

210 file mymail;

211 run;

212 %end;

213 %mend %export;

What Tom means is:

....

%mend;

%export

Re: Macro export - help please ....

Cello23 — Thu, 07 Jun 2012 07:25:16 GMT

Thank's !!!

Re: Macro export - help please ....

Tom — Thu, 07 Jun 2012 20:09:42 GMT

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.