Few remarks:
1) In your code you are overriding your input by the output.
Better way is to assign a different name, in order not to force restart from beginning.
data step9.Patstat_total_hrm_Step3;
set step9.Patstat_total_hrm_Step3;
2) It is difficult to consult without test data.
I assumed it is the same as in your previous post.
3) Debug your code out of a macro program, by:
%let company_name = <any name to test> ;
data check;
set step9.Patstat_total_hrm_Step3;
RegExID = prxparse('/<.*>|\[.*\]|\(.*\)|\{.*\}|".*"|''.*''/');
start=1;
stop=length(&COMPANY_NAME._Step23);
call prxnext(RegExID, start, stop, &COMPANY_NAME._Step23, pos, length);
do while (pos > 0);
&COMPANY_NAME._inB = substr(&COMPANY_NAME._Step23, pos+1, length-2);
&COMPANY_NAME._noB = prxchange('s/<.*>|\[.*\]|\(.*\)|\{.*\}|".*"|''.*''/ /', -1, &COMPANY_NAME._Step23);
call prxnext(RegExID, start, stop, &COMPANY_NAME._Step23, pos, length);
end;
drop RegExID pos length start stop;
run;
proc sql;
create table PATSTAT3&no. as
select distinct
&COMPANY_NAME.,
&COMPANY_NAME._Step23,
&COMPANY_NAME._inB,
&COMPANY_NAME._noB
from check /* step9.Patstat_total_hrm_Step3 */
where &COMPANY_NAME._inB ne ''
;
quit;
4) You got notes/errors, in different observations, like:
NOTE: Invalid third argument to function SUBSTR at line 2 column 106.HRM_L2=''AKTAL-LTD PERSON_CTRY_CODE=KZ PERSON_NAME=''AKTAL-LTD' SECTOR=COMPANY PERSON_ADDRESS=HRM_L2_Step23=''AKTAL-LTD PERSON_NAME_Step23=''AKTAL-LTD' HRM_L2_B= HRM_L2_address=chflagHRM_L2_Bv23=0 PERSON_NAME_B= PERSON_NAME_address= chflagPERSON_NAME_Bv23=0 HRM_L2_B_Step23=PERSON_NAME_B_Step23= RegExID=1 start=3 stop=11 pos=0 length=0 HRM_L2_inB='AKTAL-LTDHRM_L2_noB=AKTAL-LTD _ERROR_=1 _N_=2035185
the only line with substr function is:
do while (pos > 0);
&COMPANY_NAME._inB = substr(&COMPANY_NAME._Step23, pos+1, length-2);
where 3rd argument is defined as length-2, while, according to the note, length=0 and length-2 is negative, an invalid length.
You should check those observation (for example _N_=2035185) why is length=0. Is there no company name ?
Add code to deal with those observations.
5) You assigned argument no= in the macro but never used it (&no does not exist in your code).
What you intended to do with it ?
