Inserting into DB2 fom SAS dataset with passthrough SQL

Question

I'm still new to SAS and DB2. I have a DB2 Table with a column that stores values encoded as timestamps. I'm trying to load data onto this column from a SAS data set in my Work directory. Some of these timestamps, however, correspond to dates before 01-01-1582 and can not be stored as datetime values in SAS. They are instead stored as strings.

This means that if I want to load these values onto the DB2 table I must first convert them to timestamp with the TIMESTAMP() DB2 function, which, as far as I know, requires passthrough SQL with an execute statement (as opposed to the SAS ACCESS libname method). For instance, in order to write a single value I do the following:

PROC SQL;
    connect to db2 (user = xxxx database = xxxx password = xxxx);
    execute (insert into xxxx.xxxx (var) values (TIMESTAMP('0001-01-01-00.00.00.000000'))) by db2;
    disconnect from db2;
quit;

How can I achieve this for all values in the source data set? A select ... from statement inside the execute command doesn't work because as far as I know I can't reference the SAS Work directory from within the DB2 connection.

Ultimately I could write a macro that executes the PROC SQL block above and call it from within a data step for every observation but I was wondering if there's an easier way to do this. Changing the types of the variables is not an option.

Thanks in advance.

user2877959 user2877959 · Accepted Answer · 2017-08-14T11:59:48

A convoluted way of working around that would be to use call execute:

data _null_;
set sas_table;
call execute("PROC SQL;
              connect to db2 (user = xxxx database = xxxx password = xxxx);
              execute (
                 insert into xxxx.xxxx (var)
                 values (TIMESTAMP('"||strip(dt_string)||"'))
                ) by db2;
              disconnect from db2;
              quit;");
run;

Where sas_table is your SAS dataset containing the datetime values stored as strings and in a variable called dt_string.

What happens here is that, for each observation in a dataset, SAS will execute the argument of the execute call routine, each time with the current value of dt_string.

Another method using macros instead of call execute to do essentially the same thing:

%macro insert_timestamp;
  %let refid = %sysfunc(open(sas_table));
  %let refrc = %sysfunc(fetch(&refid.));
  %do %while(not &refrc.);
    %let var = %sysfunc(getvarc(&refid.,%sysfunc(varnum(&refid.,dt_string))));

    PROC SQL;
      connect to db2 (user = xxxx database = xxxx password = xxxx);
      execute (insert into xxxx.xxxx (var) values (TIMESTAMP(%str(%')&var.%str(%')))) by db2;
     disconnect from db2;
    quit;

    %let refrc = %sysfunc(fetch(&refid.));
  %end;
  %let refid = %sysfunc(close(&refid.));
%mend;
%insert_timestamp;

EDIT: I guess you could also load the table as-is in DB2 using SAS/ACCESS and then convert the strings to timestamp with sql pass-through. Something like

libname lib db2 database=xxxx schema=xxxx user=xxxx password=xxxx;
data lib.temp;
set sas_table;
run;
PROC SQL;
    connect to db2 (user = xxxx database = xxxx password = xxxx);
    execute (create table xxxx.xxxx (var TIMESTAMP)) by db2;
    execute (insert into xxxx.xxxx select TIMESTAMP(dt_string) from xxxx.temp) by db2;
    execute (drop table xxxx.temp) by db2;
    disconnect from db2;
quit;

Inserting into DB2 fom SAS dataset with passthrough SQL

1 Answers