How to work with concatenation pipes "||" in insert statement

[jerry898969]

Hello,

I have an sas file that is being created in a C# application.  There is a notes field that users can populate and put carriage returns in.

When I try and run the below insert statement in sas it gives me the below error on the first set of "||"

proc sql ; 
Insert into Notes (id, text)
values(1,'test' || '0d0a'x || '' || '0d0a'x || '')
;
quit ; 

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant,
              a missing value, ), +, ',', -, MISSING, NULL, USER.

ERROR 200-322: The s00-322: The symbol is not recognized and will be ignored.s not recognized and will be ignored.

If I insert a row into this table with an empty text field then update the field with the same text it works fine.  How can I get around this and still get the desired results?

Thank you

[Tom]

Did you try having the C program concat the values and then emit the string as Hex so that you could generate.

values(1,'746573740D0A200D0A'x)

[art297]

Did you try using a set statement rather than a values statement?  e.g.:

data notes;

  informat text $30.;

  format text $30.;

  input id text &;

  cards;

15 this is a test

;

proc sql ;

  Insert into Notes (id, text)

    set id=1,

      text='test'||'xx'||'0d0a'x||'yy'||'0d0a'x||''

  ;

quit;

[jerry898969]

Hi Art,

Thank you for your reply.  I was working on a different project so I apologize for my late reply.

I have it working now by doing an insert that only populated the primary key and then did an update to populate the text. 

When I get back on the project I plan on looking into your suggestion.  I will post back once I do.

Thank you for your help.