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