BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bitterjn
Calcite | Level 5

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' | ' ' | ' ' |

  

1 ACCEPTED SOLUTION

Accepted Solutions
s_lassen
Meteorite | Level 14

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;

 

View solution in original post

7 REPLIES 7
Astounding
PROC Star
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
Calcite | Level 5
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
Meteorite | Level 14

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
Calcite | Level 5
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
Calcite | Level 5

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
Meteorite | Level 14

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
Super User
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.

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 4038 views
  • 1 like
  • 4 in conversation