/*******************************************************************/ /* Purpose: Build the SQL to create and load a table in a DBMS. */ /* This is useful when DBLOAD is not supported for the */ /* DBMS. */ /*******************************************************************/ /* In case you want to debug the macro's, uncomment this option statement right below: */ options nosymbolgen mprint nomlogic; options source2; /* Temporary file to store the data. */ /* We are using two for this code tmpbld the actual create table code tmp file that contains all the execute insert commands After the program runs, these files are left around in case you would like to browse what was built. Also make sure you assign a libname to the library where the dataset you want to push into the DBMS is */ filename tmpbld '/TECH0/sasasf/testdb2/tmp_create'; *filename tmpbld '/nfs/vilas/local/disk0/TECH/sasasf/testdb2/tmp_create'; *filename tmpbld '/dev/null'; /* if you are not going to create a table */ *filename tmp '/nfs/vilas/local/disk0/TECH/sasasf/testdb2/tmp_inserts'; filename tmp '/TECH0/sasasf/testdb2/tmp_inserts'; *libname tmplib '/nfs/vilas/local/disk0/TECH/sasasf/testdb2'; libname tmplib '/TECH0/sasasf/testdb2'; /* macro variables to provide an easy method for changing the */ /* desired information */ %let table=ALEC; /* table name to use in DBMS _CASE SENSITIVE_ */ %let libref=tmplib; /* libref of SAS DSET */ %let dset=a; /* SAS dataset you want to load into DBMS */ %let format=6.2; /* The format for sending over numeric data */ %let dbms=oracle; /* Choices are: ORACLE, SYBASE, INGRES, INFORMIX */ /* AND DB2 */ %let drop=0; /* set to 1 if you want to drop the table first */ %let create=0; /* set to 1 if you want to create a table on dbms */ /* if both drop and create are set to 0 the result */ /* is an append to an existing table */ %let cmit_frq=100; /* The number of obs processed between commits */ /* Example of DB/2 or Ingres Connect String */ *%LET CNCT_STR= DATABASE='SAMPLE'; /* Example of Informix Connect String */ *%LET CNCT_STR= DATABASE='//online/stores'; /* Example of Oracle Connect String */ %LET CNCT_STR= user='sasasf' orapw='sasasf' path='@t:trinity:o7103' ; * %LET CNCT_STR= user='sasasf' orapw='sasasf' path='@riggs_o7103' ; /* Example of Sybase Connect String */ *%LET CNCT_STR= user='my_id' sybpw='my_pw' database='personnel' ; /* Remove the formats associated with the DSET, they may cause */ /* problems with the INSERT. This does NOT fit the dynamics */ /* of the remainder of the code. This section could be moved */ /* directly before the creation of the INSERT statements with */ /* use of the generated MACRO variables. */ /* The exception to this are date variables, they must be in the */ /* datetime21.2 format for oracle to recognize them as valid dates */ /* data &libref..&dset; format price 12.; set sasuser.houses; run; */ /* Collect information from the dictionary tables - variable names */ proc sql; create view vcolumn as select * from dictionary.columns where libname=upcase("&libref") and memname=upcase("&dset"); quit; run; /* Build the CREATE TABLE statement and write to tmpbld */ %macro cre_tab; data _null_; %if &create %then %str(file tmpbld;); %else %str(file '/dev/null';); length vtype $ 20; set vcolumn end=temp_var; /* first check to see if any of the variable names conflict with varnames that are used in this data step */ if name = 'TEMP_VAR' | name = 'VTYPE' then do; put 'ERROR: The variable name TEMP_VAR must be changed in this program.'; put 'ERROR: The variable name TEMP_VAR must be changed in this program.'; put 'ERROR: The variable name TEMP_VAR must be changed in this program.'; put 'ERROR: The variable name TEMP_VAR must be changed in this program.'; end; /* nobs is actually the number of variables in dataset to be loaded */ if temp_var then call symput('nobs',compress(put(_n_,5.))); /* now store the names of the variables in macro variables var1... varn */ /* and store the variable format (which determines the type of oracle data field */ /* since SAS only has CHAR and NUM, the format is used to identify date fields */ /* and the typnn macro vars are used to control the formats on the put statements */ /* that are used to actually write out the data to the flat file to be pushed */ /* into the DBMS */ call symput('var' || compress(put(_n_,5.)),name); if index(format,'DATETIME') then do; vtype=name || ' DATE'; call symput('typ' || compress(put(_n_,5.)),'DATETIME'); end; else if index(format,'DATE') | index(format,'DDMMYY') | index(format,'MMDDYY') | index(format,'WORDDAT') | index(format,'YYMMDD') then do; vtype=name || ' DATE'; call symput('typ' || compress(put(_n_,5.)),'DATE'); end; else if type='num' then do; vtype=name || ' FLOAT'; call symput('typ' || compress(put(_n_,5.)),'FLOAT'); end; else do; vtype=name || ' CHAR' || '(' || compress(put(length,9.)) || ')'; call symput('typ' || compress(put(_n_,5.)),'CHAR'); end; if _n_ =1 then do; put "proc sql; connect to &dbms (&cnct_str);"; %if &drop %then %str(put "execute(drop table %"&table%" ) by &dbms;";); /* drop the table first */ %str(put "execute(create table %"&table%" (";); end; put vtype @@; if ~temp_var then put ','; else do; put ")) by &dbms;"; put 'quit;'; end; run; /* %INCLUDE the file generated above. This will execute the code */ /* that creates the table. */ %if &create %then %do; %include tmpbld; %put proc sql return code: &sqlxrc; %if &sqlxrc > 0 %then %put proc SQL message: &sqlxmsg; %end; %mend; %cre_tab; /*----------------------------------------------------------------*/ /* Now, we should have a table on the database side that we can */ /* now start to insert values into the table. */ /*----------------------------------------------------------------*/ %macro bldinst; data _null_; file tmp; if _n_=1 then put 'proc sql;' / "connect to &dbms (&cnct_str);" ; length tmpvar2 $ 18; set &libref..&dset end=temp_var; put %str("execute (insert into %"&table%" values("); /* the following code uses put statements to write the values to the file in the form of execute insert values(...) statements but we need to vary the format */ %do i=1 %to &nobs; %if &&typ&i = DATETIME %then %do; tmpvar2 = trim(left(put(&&var&i., datetime19.))); %str(put "TO_DATE(" "'" tmpvar2 $char18. "','DDMONYYYY:HH24:MI:SS')" @;); %end; %else %if &&typ&i = DATE %then %do; tmpvar2 = trim(left(put(&&var&i., date9.))); %str(put "TO_DATE(" "'" tmpvar2 $char9. "','DDMONYYYY')" @;); %end; %else %if &&typ&i = FLOAT %then %str(put &&var&i. &format @;); %else %if &&typ&i = CHAR %then %str(put "'" &&var&i. @+(-1)"'" @;); %else %then %put Error: No type for variable &&var&i; %if &i ^= &nobs %then %str(put ',' @;); %end; put ")) by &dbms;"; /* Now check to see how many obs have been processed and if we're on the commit frequency one, then issue a commit statement */ if mod(_n_,&cmit_frq) = 0 | temp_var then put "execute (commit) by &dbms;"; if temp_var then put 'quit;'; run; /* %INCLUDE the file that contains the SQL generated above. This */ /* execute the code and load the table. */ %include tmp; %put PROC SQL Return Code: &sqlxrc; %if &sqlxrc > 0 %then %put Message from PROC SQL: &sqlxmsg; %mend; %bldinst;