More positional parameters found error when special characters present in data

[forumsguy]

Hi there,

I have special characters in my data .. My logic is whenever there is special character insert data in my error table ...Please find code below

DATA TEST;

INPUT VAL $256. Rec_nbr key ;

DATALINES;

AGReins|Tempète - 2eme tranche 50 XS 80|Broker 1 1 .

AGReins|Engineering (XoL BDM et TRC) - 2ème Tranche 5 Xs 10|Broker 1 2 .

RUN;

%MACRO INSERT(PARAM1,PARAM2);

CREATE TABLE ERROR_TABLE (DT NUMERIC FORMAT=DATETIME 20.,VALUE VARCHAR(256),REC_NBR NUMERIC);

PROC SQL;

INSERT INTO ERROR_TABLE VALUES

(,%SYSFUNC(DATETIME()),"&PARAM2.",&PARAM1.);

QUIT;

%MEND;

%MACRO TEST;

DATA _NULL_;

length val $256;

SET TEST;

VAL1=COMPRESS(VAL);

IF Rec_nbr=. THEN

CALL EXECUTE('%INSERT('||REC_NBR||', '||VAL1||')');

RUN;

%END;

%MEND;

%TEST;

[Tom · Accepted solution]

In general if you are passing values that could include delimiters then quote the values.  In this case as you are using the strings ultimately in regular SAS code just put quotes around the text.

CALL EXECUTE('%INSERT('||quote(REC_NBR)||', '||VAL1||')');

Then inside the macro remove the quotes.

The solution to your real problem is to not use macro code for this type of problem.  It is really much easier to do without it.

* Create empty error_table ;

data ERROR_TABLE ;

  attrib dt length=8 format=datetime20.;

  attrib value length=$256;

  attrib rec_nbr length=8;

  stop;

run;

data error;

  attrib dt length=8 format=datetime20.;

  attrib value length=$256;

  attrib rec_nbr length=8;

   keep dt value rec_nbr;

  set test;

   where rec_nbr=.;

   dt=datetime();

   value=compress(val);

run;

proc append base=error_table data=error;

run;

[AUTigers]

There are couple of typos in your code provided.

1. there is a extraneous comma in your insert statement.

2, per you sample data, key variable can be missing, not the rec_nbr. so i change it to REC_NBR~=. so that the call routine can be executed.

as you stated, there are might be some special characters in the VAL vars, most of time the %bquote will work if you don't have single ' or " when you invoke macro with call execute.

but if you do have special char like single ' or ", i would still wrap the var with %bquote or %nrbquote, but instead of calling call execute, I just write the macro call to an external file. later %include that file to invoke marco calls.

DATA TEST;

INPUT VAL & :$256. Rec_nbr key ;

DATALINES;

AGReins|Tempète - 2eme tranche 50 XS 80|Broker 1  1 .

AGReins|Engineering (XoL BDM et TRC) - 2ème Tranche 5 Xs 10|Broker 1  2 .

;

RUN;

proc sql;

CREATE TABLE ERROR_TABLE (DT NUMERIC FORMAT=DATETIME20.,

                     VALUE VARCHAR(256),

                  REC_NBR NUMERIC);

quit;

%MACRO INSERT(PARAM1,PARAM2);

proc SQL;

INSERT INTO ERROR_TABLE VALUES

(%sysfunc(DATETIME()),"&PARAM2.",&PARAM1.);

quit;

%MEND;

filename temp temp;

DATA _NULL_;

length val $256 cmd $500;

file temp lrecl=1000;

SET TEST;

VAL1=COMPRESS(VAL);

IF Rec_nbr ~=. THEN

cmd=cats('%INSERT(',REC_NBR,',%bquote(',VAL1,'))');

put cmd;

RUN;

%include temp/source2;