Ejecutando SAS desde EXCEL: Una Aplicación en Administración de Datos Wilson Mayorga [email protected] Copyright © 2010, SAS Institute Inc. All rights reserved.

Introducción Es una poderosa herramienta para administrar grandes volúmenes de información.

En las Empresas, muchos usuarios finales utilizan EXCEL en tareas de administración de datos que SAS realiza eficientemente

Utilizando la Tecnología de Integración llamada IOM es Posible diseñar FORMULARIOS en VISUAL BASIC que ejecuten Macros de SAS.

Copyright © 2010, SAS Institute Inc. All rights reserved.

Introducción En el área de Actuaría de LIBERTY SEGUROS estamos mejorando los procesos de administración de DATOS para reducir tiempo en tareas operativas y generar información precisa rápidamente. El Sistema de Información que deseamos es:

DataWarehouse

SAS Access for ODBC

Copyright © 2010, SAS Institute Inc. All rights reserved.

 IOM  Macros SAS  Programación VB

Introducción  ALGUNAS APLICACIONES: • Diseño de Formularios para Ejecutar Macros de SAS • Uso de IOM Data Provider para Importar SAS Datasets a Excel • Uso de IOM Data Provider para Crear Tablas Dinámicas de Excel conectadas a SAS Datasets. • Ejecución de Comandos SAS desde Celdas de Excel

 Asumiremos que tanto SAS como los Datasets son locales; aunque, es posible ejecutar las mismas operaciones cuando algún elemento se encuentra en servidor.

Copyright © 2010, SAS Institute Inc. All rights reserved.

Definiciones y Requerimientos ¿Qué es IOM?

Integrated Object Model

 Es una de las tecnologías de Integración de SAS que permite manejar sus capacidades desde otro software mediante una infraestructura de Cliente/ Servidor.

 Existen 2 tipos de IOM Servers. Uno de ellos es el SAS WORKSPACE SERVER.  La Interacción entre SAS y un programa bajo Windows (Por ejemplo Visual Basic) se realiza utilizando Microsoft Component Object Model (COM).  El IOM de SAS corresponde a un tipo de COM llamado ActiveX component. Copyright © 2010, SAS Institute Inc. All rights reserved.

Definiciones y Requerimientos ¿Requerimientos Mínimos – Referencias en VB? Asociado a los ActiveX components existen un conjunto de métodos y propiedades agrupadas en algo llamado type library.

Referencias:     Copyright © 2010, SAS Institute Inc. All rights reserved.

SAS: Integrated Object Model (IOM). SASObjectManager (Para SAS 9.X). SASWorkspace Manager (Para SAS 9.X, 8.X) Microsoft ActiveX Data

Ejemplos: Ejemplo 1: Formularios en VB para Ejecutar MACROS de SAS

Copyright © 2010, SAS Institute Inc. All rights reserved.

Ejemplo 1: Paso 1: Crear Formulario en VB y Macro en SAS (Coincidencia en Parámetros) %macro filtro(origen,destino,variable,condicion);

data a1.&destino; set a1.&origen; if &variable &condicion; run; %mend;

origen = TextBox_origen.Value destino = TextBox_destino.Value variable = ComboBox_variable.Value condicion = TextBox_condicion

Copyright © 2010, SAS Institute Inc. All rights reserved.

Ejemplo 1: Paso 1: Crear Formulario en VB y Macro en SAS %macro reporte(fila,columna,variable,indicador); %if &indicador=1 %then %do; proc summary data=a1.Ejemplo print sum; class &fila &columna; var &variable;run;%end; %if &indicador=2 %then %do; proc tabulate data=a1.Ejemplo class &fila &columna;var &variable; table (&fila all),(&columna)*&variable*SUM=' '*f=comma9.0;run;%end; %mend;

Copyright © 2010, SAS Institute Inc. All rights reserved.

If Opt_summary.Value = True Then indicador_reporte = 1 Else indicador_reporte = 2 End If fila = ComboBox_ejeX.Value columna = ComboBox_ejeY.Value variable = ComboBox_variable2.Value

Ejemplo 1: Paso 2: Definir el Worskpace utilizando el Object Manager Un SAS Workspace object representa una sesión de SAS. Para crear el Workspace, se utiliza Object Manager, que es un tipo de ActiveX component. Dim A As New SASObjectManager.ObjectFactory Dim B As SAS.Workspace Set B = A.CreateObjectByServer("", True, Nothing, "", "") A es el nombre del Object Manager y B es el nombre del Workspace creado. Nota: Object Manager es la interface disponible en SAS 9.X. En versiones anteriores la interface es WORKSPACE MANAGER y el comando es: Dim A As New SASWorkspaceManager.WorkspaceManager Dim B As SAS.Workspace Set B = A.Workspaces.CreateWorkspaceByServer("Local", VisibilityProcess, Nothing, "", "", errorstring)

Copyright © 2010, SAS Institute Inc. All rights reserved.

Ejemplos Paso 3: Definir “Comandos de Servicio” Se necesitarán object-variables adicionales para mantener referencias a objetos creados dentro del SAS Workspace. Estos serán declarados dentro de la programación con el comando StoredProcessService.

Dim C As SAS.StoredProcessService Set C = B.LanguageService.StoredProcessService C.Repository = "file:“c:\conectar sas excel"\” C es el nombre del objecto definido como ProcessService y B es el nombre del Workspace creado.

Copyright © 2010, SAS Institute Inc. All rights reserved.

Ejemplos Paso 4: Ejecutar SAS Macro en la Sintaxis de Visual Basic El comando LanguageService.Submit permite ejecutar comandos SAS, incluyendo sintaxis de SAS Macro:

C.Execute "macro_reporte", "“ B.LanguageService.Submit ("%reporte(" & fila & "," & columna & "," & variable & "," & indicador_reporte & ");”) C es el nombre del objecto definido Object Manager y B es el nombre del Workspace creado. Paso 5: Imprimir Resultados en Hojas de Excel Si se desea vincular una Dataset de SAS a hojas de Excel, se utiliza IOM PROVIDER; Para imprimir Output de un Proc de SAS ejecutado en VB se utiliza LanguageService.FlushListLines y FlushLog para observar el LOG de la ejecución SAS. Copyright © 2010, SAS Institute Inc. All rights reserved.

Ejemplos Cómo se ve la Sintaxis en VB:

Copyright © 2010, SAS Institute Inc. All rights reserved.

Ejemplos Ejemplo 2: Uso de IOM PROVIDER OLE DB es un conjunto de interfaces incluidas dentro de the Microsoft Open Database Connectivity (ODBC) que permiten la conexión, mediante un protocolo estándar, para que diferentes aplicaciones accedan a los datos de otras. SAS IOM Data Provider es un OLE DB que soporta el acceso de datos desde SAS. La conexión IOM es posible activarla mediante programación, por ejemplo: Dim obConnection As New ADODB.Connection obConnection.Provider = "sas.LocalProvider" obConnection.Properties("Data Source") = "c:\conectar sas excel\Ejemplo" obConnection.Open A continuación mostraremos cómo generar una conexión mediante el menú de Excel: Copyright © 2010, SAS Institute Inc. All rights reserved.

Ejemplos Ejemplo 2: Uso de IOM DATA PROVIDER 1. 2. 3. 4. 5. 6. 7. 8. 9. 10.

Obtener Datos Externos – De Otras Fuentes Otro o Avanzado Pestaña Provider – SAS OIM Data Provider 9.2 Pestaña All – SAS Machine DNS Name – Edit Value Escribir localhost Pestaña All – SAS WorkSpace Init Script – Edit Value Escribir libname a1 ‘c:\conectar sas excel’; Seleccionar Tabla Asignar Nombre a la conexión Finalizar

Copyright © 2010, SAS Institute Inc. All rights reserved.

Ejemplos Ejemplo 2: Uso de IOM PROVIDER

Copyright © 2010, SAS Institute Inc. All rights reserved.

Ejemplos Ejemplo 3: Ejecución de Comandos SAS En Celdas de Excel

Copyright © 2010, SAS Institute Inc. All rights reserved.

Bibliografía Referencias:  [1] SAS® 9.2 Integration Technologies Windows Client Developer’s Guide.  [2] SAS® 9.2 Providers for OLE DB Cookbook.  [3] Using IOM and Visual Basic in SAS Program Development. Greg Silva.  [4] Make Bill Gates and Dr. Goodnight Run Your SAS Code: Using VBA, ADO and IOM to Make Excel and SAS Play Nice. Ted Conway.

Copyright © 2010, SAS Institute Inc. All rights reserved.

Gracias Wilson Mayorga [email protected]

Copyright © 2010, SAS Copyright Institute©Inc. 2010, All SAS rights Institute reserved. Inc. All rights reserved.