Help using Base SAS procedures

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

Reply
Super Contributor
Posts: 400

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

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

Super User
Super User
Posts: 7,050

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

Posted in reply to jerry898969

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)

PROC Star
Posts: 7,474

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

Posted in reply to jerry898969

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;

Super Contributor
Posts: 400

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

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.

Ask a Question
Discussion stats
  • 3 replies
  • 1255 views
  • 0 likes
  • 3 in conversation