Using a Macro to pass dynamic data to DB2

deleted_user — Fri, 04 Jan 2008 11:22:21 GMT

Hi all

I have created a macro to pass data into a DB2 table. The macro creates a number of variables using the Select and INTO syntax e.g Part1:-PartN.

The issue I am having is that when I try and update a DB2 table and the contents of the variable has an apostrope in it, the macro fails. E.g " Driver's manual " - the 's causes the failure.

I tried using %BQUOTE but that simply reads up to Driver and then the log says
ILLEGAL SYMBOL "'S MANUAL

I even tried assigning the output of %BQuote to a variable and using that variable to update the table dynamically but that does not work as well.

How can I get rid of the apostrophe ?

Thanks
Shelton. Message was edited by: sdcruz

Re: Using a Macro to pass dynamic data to DB2

deleted_user — Fri, 04 Jan 2008 14:28:26 GMT

Could you provide a more specific example of the code? Message was edited by: Chuck

Re: Using a Macro to pass dynamic data to DB2

Cynthia_sas — Fri, 04 Jan 2008 19:40:20 GMT

Hi:
I always find it useful to figure out how something works without involving the SAS Macro facility, before I try to introduce anything macro into the code.

For example, let's say I have this data, WORK.ALLCOMMENT:
[pre]
NAME COMMENT
---- ------------------------------------------------
alan Good course. Needs an email example.
barb I think the course could have been a day longer.
carl Does all of this work on the mainframe?
dan The examples were very good.
[/pre]

How would I use the INSERT statement to add character data with an apostrophe or quote in the COMMENT variable value??
This is the syntax I'd use:
[pre]
proc sql;
insert into work.allcomment
values("zach","Test Comment with test's of apos'trophe");
quit;

proc print data=work.allcomment;
title "New Row";
run;

[/pre]

Which results in this:
[pre]
New Row

Obs name comment

1 alan Good course. Needs an email example.
2 barb I think the course could have been a day longer.
3 carl Does all of this work on the mainframe?
4 dan The examples were very good.
5 zach Test Comment with test's of apos'trophe

[/pre]

So now, the question becomes, how would I modify the INSERT statement to use macro variables to supply the text strings for NAME and COMMENT?? Assuming that I have created the following macro variables &INAME and &ICOMMENT (no matter how I created them -- with INTO or with CALL SYMPUT), this syntax worked for me:

[pre]
** my macro variables;
***************************************************************;
*** Macro ;
*** Variable Value ;
*** &INAME eliza ;
*** &ICOMMENT This was the best course I've ever taken. ;
************************************************************** ;

proc sql;
insert into work.allcomment
values("&iname","&icomment");
quit;

proc print data=work.allcomment;
title "New Row with quote";
run;

[/pre]

And I get these results (If I run this code after I add Zach's info:
[pre]
New Row with quote

Obs name comment

1 alan Good course. Needs an email example.
2 barb I think the course could have been a day longer.
3 carl Does all of this work on the mainframe?
4 dan The examples were very good.
5 zach Test Comment with test's of apos'trophe
6 eliza This was the best course I've ever taken.

[/pre]

Of course, I have very simple data with very simple quotes. All my quotes in the character string are single quotes and so I am able, in the INSERT, to just use double quotes around the macro variable because my single quotes inside the double quotes will correctly resolve into 1 character string. It would be a mistake to leave off the double quotes or to only use a macro quoting function in place of the double quotes, because the working SAS program needs to have a quoted string to insert a character value into a table....and I couldn't use single quotes around my string because 1) my string contains single quotes and 2) I need the macro variables to resolve and they won't resolve in single quotes.

If you have a more complicated quoting situation, such as mixed single and double quotes in your character variable value, then you may need to get into more complex quoting functions. I know of no better resource on quoting functions than the macro documentation, which contains several tables that show the list of quoting functions and when each quoting function is best used.

Or, you could contact Tech Support with your specific data/code question.

One last thought. In order to do an INTO to create your macro variables, that implies that you have the data you want already in a SAS dataset. Is there a particular reason you want to use the Macro facility??? For example, let's say that there's a file called WORK.NEWCOMMENT, which contains the following observations:
[pre]
WORK.NEWCOMMENT

Obs name comment

1 eliza This was the best course I've ever taken.
2 frank None
3 george No Comment

[/pre]

And you only want to add Eliza's information to the ALLCOMMENT file -- then this syntax works without using SAS Macro at all:

[pre]

proc sql;
INSERT INTO work.allcomment
SELECT * from work.newcomment
where comment ne "None" and comment ne "No Comment";
quit;

proc print data=work.allcomment;
run;

[/pre]
cynthia

topic Re: Using a Macro to pass dynamic data to DB2 in SAS Enterprise Guide

Using a Macro to pass dynamic data to DB2

Re: Using a Macro to pass dynamic data to DB2

Re: Using a Macro to pass dynamic data to DB2