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

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

jerry898969 — Sat, 07 Dec 2013 03:30:42 GMT

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

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

Tom — Sat, 07 Dec 2013 15:17:55 GMT

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)

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

art297 — Sat, 07 Dec 2013 15:41:35 GMT

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;

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

jerry898969 — Tue, 11 Feb 2014 16:43:37 GMT

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.