ejemplos de macros en sas

"Guardar como" y "Cerrar archivo" sin usar macro

Question

Normalmente tengo un código macro sas que ejecuta automáticamente las macros "Guardar como" y "Cerrar archivo" en la hoja de cálculo de Excel al ejecutar el código sas y después de completar los datos en el archivo Excel.

El problema que tengo es que el archivo Excel que tengo ahora tiene ciertas macros que no puedo editar porque están protegidas por contraseña y tampoco puedo agregar ninguna macro "Guardar como" y "Cerrar archivo". ¿Hay alguna manera de directamente "Guardar como" y "Cerrar archivo" con la ayuda de sas y sin usar la macro.

Answer 1

Claro que sí Puedes usar DDE para lograr eso. A continuación hay 3 macros que deberían hacer exactamente lo que pides. Agréguelos a su biblioteca de macro de llamada automática SAS:

/******************************************************************************
** PROGRAM:  MACRO.DDE_SAVE_AND_CLOSE.SAS
**
** DESCRIPTION: SAVES THE CURRENT EXCEL FILE AND THEN CLOSES IT.  IF THE FILE
**              ALREADY EXISTS IT WILL BE OVERWRITTEN.
**
** PARAMETERS: iSAVEAS: THE DESTINATION FILENAME TO SAVE TO.
**             iType  : (OPTIONAL. DEFAULT=BLANK). 
**                      BLANK = XL DEFAULT SAVE TYPE
**                          1 = XLS DOC - OLD SCHOOL! PRE OFFICE 2007?
**                         44 = HTML - PRETTY COOL! CHECK IT OUT... 
**                         51 = XLSX DOC - OFFICE 2007 ONWARDS COMPATIBLE?
**                         57 = PDF
** 
** NOTES:  IF YOU ARE GETTING A DDE ERROR WHEN RUNNING THIS MACRO THEN DOUBLE
**         CHECK YOU HAVE PERMISSIONS TO SAVE WHERE YOU ARE TRYING TO SAVE THE
**         FILE.
** 
*******************************************************************************
** VERSION:
** 1.0 ON: 01APR10 BY: RP
**     CREATED.  
** 1.1 ON: 19MAY10 BY: RP
**     ADDED ITYPE OPTION.
** 1.2 ON: 20JUL10 BY: RP
**     DELAYED TURNING ERROR BACK ON AS WAS GETTING UNNECESSARY ERRORS IN 
**     OFFICE07
** 1.3 ON: 17AUG10 BY: RP
**     CHANGED TO FILE. CLOSE AS IT WOULD CLOSE TWO WORKBOOKS BEFORE.
** 1.4 ON: 21JUL11 BY: RP
**     STOPPED E-R-R-O-R LITERAL FROM SHOWING IN LOG
******************************************************************************/

%macro dde_save_and_close(iSaveAs=,iType=);
  %local iDocTypeClause;

  %let iDocTypeClause=;
  %if "&iType" ne "" %then %do;
    %let iDocTypeClause=,&iType;
  %end;

  filename cmdexcel dde ''excel|system'';
  data _null_;
    length str_line $200;
    file cmdexcel;

    put ''[error(false)]'';
    put "%str([save.as(%"&iSaveAs%"&iDocTypeClause)])";
    put ''[file.close(0)]'';

    str_line = cats("[e","rror(true)]");
    put str_line;
  run;
  filename cmdexcel clear;

%mend;
/*%dde_save_and_close(iSaveAs=d:/rrobxltest, iType=44);*/


/******************************************************************************
** PROGRAM:  MACRO.DDE_SAVE_AS.SAS
**
** DESCRIPTION: SAVES THE CURRENT EXCEL FILE.  IF THE FILE
**              ALREADY EXISTS IT WILL BE OVERWRITTEN.
**
** PARAMETERS: iSAVEAS: THE DESTINATION FILENAME TO SAVE TO.
**             iType  : (OPTIONAL. DEFAULT=BLANK). 
**                      BLANK = XL DEFAULT SAVE TYPE
**                          1 = XLS DOC - OLD SCHOOL! PRE OFFICE 2007?
**                         44 = HTML - PRETTY COOL! CHECK IT OUT... 
**                         51 = XLSX DOC - OFFICE 2007 ONWARDS COMPATIBLE?
**                         57 = PDF
** 
** NOTES:  IF YOU ARE GETTING A DDE ERROR WHEN RUNNING THIS MACRO THEN DOUBLE
**         CHECK YOU HAVE PERMISSIONS TO SAVE WHERE YOU ARE TRYING TO SAVE THE
**         FILE.
** 
*******************************************************************************
** VERSION:
** 1.0 ON: 01APR10 BY: RP
**     CREATED.  
******************************************************************************/

%macro dde_save_as(iSaveAs=,iType=);
  %local iDocTypeClause;

  %let iDocTypeClause=;
  %if "&iType" ne "" %then %do;
    %let iDocTypeClause=,&iType;
  %end;

  filename cmdexcel dde ''excel|system'';
  data _null_;
    length str_line $200;
    file cmdexcel;
    put ''[error(false)]'';
    put "%str([save.as(%"&iSaveAs%"&iDocTypeClause)])";
    str_line = cats("[e","rror(true)]");
    put str_line;
  run;
  filename cmdexcel clear;

%mend;
/*%dde_save_as(iSaveAs=d:/rrobxltest, iType=44);*/

/******************************************************************************
** PROGRAM:  MACRO.DDE_CLOSE_WITHOUT_SAVE.SAS
**
** DESCRIPTION: CLOSES EXCEL WITHOUT SAVING THE FILE
**
** PARAMETERS: NONE
** 
*******************************************************************************
** VERSION:
** 1.0 ON: 18MAY10 BY: RP
**     CREATED.  
******************************************************************************/

%macro dde_close_without_save();
  filename cmdexcel dde ''excel|system'';
  data _null_;
    file cmdexcel;
    put ''[close(0)]'';
  run;
  filename cmdexcel clear;
%mend;
/*%dde_close_without_save;*/