Using SAS to insert rows containing nulls and blanks into database

[bitterjn]

I posted this question already on stackoverflow but couldn't get an answer, so I hope that the experts here can help me.

I have a db2 table DBTable with columns A, B, C (all of type varchar) which is linked to a library lib in SAS. I use SAS to generate a dataset ValuesForA with one column whose content I want to write into the column A of DBTable with the additional requirement that the the column for B is filled with ' ' (blank) and the column for C with (null). So the DBTable should look something like this:

|  A  |  B  |    C   |
======================
| 'x' | ' ' | (null) |
| 'y' | ' ' | (null) |
| 'z' | ' ' | (null) |

I cannot find a way how to acchieve this as SAS as it treats blanks as null.

---

The simple approach specifying B as " " just fills this column with (null). I also tried to use the nullchar=no option and not specifying a value for C:

proc sql;
   insert into lib.DBTable
      (nullchar=no, A, B)
   select
       A, " " as B
   from ValuesForA;
quit;

however the column C is then also filled with blanks

|  A  |  B  |  C  |
===================
| 'x' | ' ' | ' ' |
| 'y' | ' ' | ' ' |
| 'z' | ' ' | ' ' |

[Astounding]

Worth a shot: try hard coding the equivalent of a null character as a literal string. For a character field C,

'00'x as C

[bitterjn]

Thanks for the tipp 🙂
But it does not seem to work. Column C is still not filled with (null) (in DbVisualizer the character in column C is displayed as a square btw)

[s_lassen · Accepted solution]

AFAIK, there is no way round that using a data step.

I see two possibilities:

1. Create a temporary table in DB2 (just the A values, the others are constant), and use pass-through SQL to insert the values in the permanent table.
2. Write a temporary SAS program to insert the values using pass-through SQL.

Solution 1:

Data lib.ValuesForA;
  set ValuesForA(keep=a);
run;

proc sql;
  connect to DB2 <connect options>;
  execute by DB2(
    insert into DBTable (a,b) select a,' ' from ValuesForA
    );
  drop table lib.ValuesForA;
quit;

Solution 2:

Filename tempsas temp;

Data _null_;
  file tempsas;
  set ValuesForA end=done;
  if _N_=1 then put
    'proc sql;' /
    '  Connect to DB2 <connect options>;'  /
    '  execute by DB2(' /
    '    insert into DBTable (a,b)'
    ;
  put
    "       values ('" a +(-1) "',' ')" @;
  if done then put
    / '      );' /
     'quit;'
    ;
  else put ',';
run;

%include tempsas;

[bitterjn]

Thanks for the reply.
Solution 1: I think I would prefer this solution, but in my case that's not possible as I don't have rights to create tables in the database.
Solution 2: This seems to be a solution that stays on the SAS-side as long as it can, which for me is actually quite good. But I have troubles to understand the code. Can you point out to me where the content of ValuesForA get's transfered into the server session. I assume it's the line ending in an @, so maybe you can tell me what kind of syntax this is or where I can read about that. I don't want to hurt the companie's data base 😛

[bitterjn]

Again s_lassen thanks for your answer.

For further reference I want to explain for other Sas-novices like me how solution 2 works:

/* *** Create a temporary 'document' with the name tempsas *** */
Filename tempsas temp;

/* *** Loop throuhg ValuesForA and write into tempsas. *** */
/* (The resulting tempsas is basically an SQL insert statement.
    All observations are written in one big values statement) */
Data _null_;
  file tempsas;
  set ValuesForA end=done;
  if _N_=1 then put
    'rsubmit;' /  
    'proc sql;' /
    '  Connect to DB2(<connect options>);'  /
    '  execute by DB2(' /
    '    insert into DBTable (a,b)'
    '    values'
    ;
  put
    "       ('" a +(-1) "',' ')" @;  /* "a" is the observation ValuesForA.a
                                        "+(-1)" removes the trailing blank set by the put statement
                                        "@" writes the next put statement in the same line
  if done then put
     /
     '      );' /
     'quit;' /
     'endrsubmit;'
    ;
  else put ',';
run;

/* *** Run the code in tempsas *** */
%include tempsas;

For debugging and understanding this code one can also write the result into a file (change filename tempSas temp; to filename tempsas disk "example.txt"; and remove %include tempsas;) and see the resulting code in that file.

[s_lassen]

What I normally do when writing temporary programs like this is

1. submit the code to create the program
2. take a look at what I have generated ("include tempsas" on the command line in a SAS text editor, or open Explorer -> File Shortcuts, and take a look at the "Tempsas" file there
3. If it looks OK, take a shot at submitting the code - in many cases, I have a number of statements in the file, so I start with first one to see if that works - in this particular case, the whole stuff is one SQL statement, so we have to do it in one fell swoop (I love that expression!).
4. If this works OK, add the %include line to the final program

But anyway, I am glad you could use my suggestion. Although I would also prefer the first solution. I think there is a way to create temporary tables in DB2 (as in most databases, you may want to talk to the DBAs about that), but you may not have the rights to do that either. But if the amount of data is not too large, the second solution should work OK.

[Ksharp]

That could not be, SAS take NULL and BLANK as the same thing due to in sas BLANK is just NULL.
the only way do it is using Pass-Through as s_lassen did.