SAS/Macro Exec SQL Function

From sasCommunity
< SAS
Jump to: navigation, search

Description

We often query DB2 tables from SAS/Mainframe and I was more than tired to see 6-10 lines of code for each SQL statement when I arrived here. So I decided to make a quick SAS/Macro function that just does it for me. With usage, I finally added a check for the error checking and a wrapping "if" around the Options OBS depending on the job class (M, K, W, K).

Code


   /************************************************************************/   
   /* Macro "exec_sql" recoits un nom DATA et un SQL en entree             */   
   /*   et l' execute ... un DATA SAS est ainsi cree pour traitement       */   
   /* Paramètres:                                                          */   
   /*    1 = data : nom du data que lon veut creer ou ecraser.             */   
   /*    2 = sql : requete SQL a executer                                  */   
   /************************************************************************/   
%macro exec_sql(data, sql);                                                     
 *OPTIONS OBS=30000;                                                            
  PROC SQL;                                                                     
  CONNECT TO DB2 (SSID=DB2P);                                                   
  EXECUTE (SET CURRENT SQLID = 'PCSP0099') BY DB2;                              
  CREATE TABLE &data as SELECT * FROM CONNECTION TO DB2 ( &sql );               
  %if &SQLXRC ne 0 %then %do;                                                   
      %PUT Erreur No: &SQLXRC;                                                  
      %PUT Erreur Message: &SQLXMSG;                                            
  %end;                                                                         
  DISCONNECT FROM DB2;                                                          
  QUIT;                                                                         
 *OPTIONS OBS=max;                                                              
%mend;       


Usage

%let sql=%bquote(select * from ENV.TABLENAME);
%exec_sql(DATASASNAME, &sql);

By

User:Mathieu