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;
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;
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.