/*******************************************************************/ /* proc sql; connect to oracle (user='sasasf' orapw='sasasf' path='@riggs_o7103'); disconnect from oracle;quit; */ /* 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 the option statement directly below: */ options nosymbolgen nomprint nomlogic nosymbolgen; 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 '/TECH/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 '/TECH/sasasf/testdb2/tmp_inserts'; *libname tmplib '/nfs/vilas/local/disk0/TECH/sasasf/testdb2'; libname tmplib '/TECH/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_ */ /* enclose in double quotes for oracle and sybase */ %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=informix; /* Choices are: ORACLE, SYBASE, INGRES, INFORMIX */ /* AND DB2 */ %let drop=1; /* set to 1 if you want to drop the table first */ %let create=1; /* 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 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' ; /* Example of Informix Connect String */ %LET CNCT_STR= DATABASE='//online7/stores7'; /* 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 $ 35; 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' | name = 'SINGLEQT' | name = 'DOUBLEQT' 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 || ' DATETIME YEAR TO SECOND'; 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 || ' VARCHAR' || '(' || 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);"; /* drop the table first if &drop is > 0 per %let stmt above */ %if &drop %then %str( put "execute(drop table &table ) by &dbms;"; ); %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 $ 22; set &libref..&dset end=temp_var; put "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; if &&var&i = . then put 'NULL' @; else do; tmpvar2 = compress(put(datepart(&&var&i.), yymmdd10.)) || ' ' || compress( put(timepart(&&var&i.), time8.) ); put "'" tmpvar2 $char22. "'" @; end; %end; %else %if &&typ&i = DATE %then %do; if &&var&i = . then put 'NULL' @; else put "'" &&var&i mmddyy10. "'" @; %end; %else %if &&typ&i = FLOAT %then %do; if &&var&i = . then put 'NULL' @; else put &&var&i. &format @; %end; %else %if &&typ&i = CHAR %then %do; doubleqt = index(&&var&i.,'"'); singleqt = index(&&var&i.,"'"); if singleqt & doubleqt then do; put 'ERROR: A string contains both single and double quotes'; put 'ERROR: A string contains both single and double quotes'; put 'ERROR: A string contains both single and double quotes'; end; else if doubleqt then put "'" &&var&i. @+(-1)"'" @; else put '"' &&var&i. @+(-1)'"' @; %end; %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 */ /* not needed for informix */ /* 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;