Dear all,
how to use the Marco to repeat code,
I am trying to repeat a process 10 times by the following code,
%macro PreviousName;
%do i=1 %to 10;
data Sa_step2.House_company_base;
set Sa_step2.House_company_base;
_PreviousName_&i_CompanyName=upcase(_PreviousName_&i_CompanyName);
call scan(_PreviousName_&i_CompanyName, -1, position, length);
if scan(_PreviousName_&i_CompanyName,-1) in ('LTD' ,'LTD.','LIMITED.') then substr(_PreviousName_&i_CompanyName,position)='LIMITED';
drop position length;
run;
data sa_Step2.Patstat_gb_hrm;
set Step2.Patstat_gb_hrm;
rename PERSON_NAME=_PreviousName_&i_CompanyName;
run;
proc sort data=sa_Step2.Patstat_gb_hrm;
by _PreviousName_&i_CompanyName;
run;
proc sort data=Sa_step2.House_company_base;
by _PreviousName_&i_CompanyName;
run;
data STEP6.CompanyHouse_Patstat&i step4.Patstat_gb_hrm&i step3.CompanyHouse&i;
merge Step2.Patstat_gb_hrm (in=ina)
Sa_step2.House_company_base (in=inb)
;
by _PreviousName_&i_CompanyName;
if ina and inb then output STEP6.CompanyHouse_Patstat&i;
else if ina then output step4.Patstat_gb_hrm&i;
else if inb then output step3.CompanyHouse&i;
run;
%end;
%mend PreviousName;
%PreviousName
run;
But it doesn't work.
926 %macro PreviousName;
927 %do i=1 %to 10;
928
929 data Sa_step2.House_company_base;
930 set Sa_step2.House_company_base;
931 _PreviousName_&i_CompanyName =upcase(_PreviousName_&i_CompanyName);
932 call scan(_PreviousName_&i_CompanyName, -1, position, length);
933 if scan(_PreviousName_&i_CompanyName,-1) in ('LTD' ,'LTD.','LIMITED.') then
933! substr(_PreviousName_&i_CompanyName,position)='LIMITED';
934 drop position length;
935 run;
936
937 data sa_Step2.Patstat_gb_hrm;
938 set Step2.Patstat_gb_hrm;
939 rename PERSON_NAME=_PreviousName_&i_CompanyName;
940 run;
941
942 proc sort data=sa_Step2.Patstat_gb_hrm;
943 by _PreviousName_&i_CompanyName;
944 run;
945 proc sort data=Sa_step2.House_company_base;
946 by _PreviousName_&i_CompanyName;
947 run;
948
949 data STEP6.CompanyHouse_Patstat&i step4.Patstat_gb_hrm&i step3.CompanyHouse&i;
950 merge Step2.Patstat_gb_hrm (in=ina)
951 Sa_step2.House_company_base (in=inb)
952 ;
953 by _PreviousName_&i_CompanyName;
954 if ina and inb then output STEP6.CompanyHouse_Patstat&i;
955 else if ina then output step4.Patstat_gb_hrm&i;
956 else if inb then output step3.CompanyHouse&i;
957 run;
958
959
960 %end;
961 %mend PreviousName;
962
963 %PreviousName
NOTE: Line generated by the invoked macro "PREVIOUSNAME".
1 data Sa_step2.House_company_base; set Sa_step2.House_company_base; _PreviousName_&i_CompanyName
--------------
180
1 ! =upcase(_PreviousName_&i_CompanyName); call scan(_PreviousName_&i_CompanyName, -1, position,
1 ! length); if scan(_PreviousName_&i_CompanyName,-1) in ('LTD'
WARNING: Apparent symbolic reference I_COMPANYNAME not resolved.
WARNING: Apparent symbolic reference I_COMPANYNAME not resolved.
WARNING: Apparent symbolic reference I_COMPANYNAME not resolved.
WARNING: Apparent symbolic reference I_COMPANYNAME not resolved.
NOTE: Line generated by the invoked macro "PREVIOUSNAME".
2 ,'LTD.','LIMITED.') then substr(_PreviousName_&i_CompanyName,position)='LIMITED'; drop position
-
356
2 ! length; run; data sa_Step2.Patstat_gb_hrm; set Step2.Patstat_gb_hrm; rename
WARNING: Apparent symbolic reference I_COMPANYNAME not resolved.
ERROR: Argument to SUBSTR must be character.
ERROR 180-322: Statement is not valid or it is used out of proper order.
NOTE 356-185: The SUBSTR pseudo-variable function does not allow character constants, expressions, or
numeric constants for the first argument.
NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
1:162 1:223
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set SA_STEP2.HOUSE_COMPANY_BASE may be incomplete. When this step was stopped
there were 0 observations and 57 variables.
WARNING: Data set SA_STEP2.HOUSE_COMPANY_BASE was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.37 seconds
cpu time 0.01 seconds
NOTE: Line generated by the invoked macro "PREVIOUSNAME".
2 data sa_Step2.Patstat_gb_hrm; set Step2.Patstat_gb_hrm; rename
2 ! PERSON_NAME=_PreviousName_&i_CompanyName; run; proc sort
-
22
200
WARNING: Apparent symbolic reference I_COMPANYNAME not resolved.
ERROR: Missing numeric suffix on a numbered variable list (_PreviousName_-i_CompanyName).
ERROR: Old and new variable name specifications for RENAME must be of the same type. Statement is
ignored.
ERROR 22-322: Syntax error, expecting one of the following: a name, -, :, ;.
ERROR 200-322: The symbol is not recognized and will be ignored.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set SA_STEP2.PATSTAT_GB_HRM may be incomplete. When this step was stopped there
were 0 observations and 7 variables.
NOTE: DATA statement used (Total process time):
real time 0.07 seconds
cpu time 0.01 seconds
22: LINE and COLUMN cannot be determined.
NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 22-322: Syntax error, expecting one of the following: a name, ;, -, :, DECENDING, DESCENDING,
DESENDING, _ALL_, _CHARACTER_, _CHAR_, _NUMERIC_.
200: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where
the error has occurred.
ERROR 200-322: The symbol is not recognized and will be ignored.
WARNING: Apparent symbolic reference I_COMPANYNAME not resolved.
ERROR: Variable _PREVIOUSNAME_ not found.
ERROR: Variable I_COMPANYNAME not found.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.04 seconds
cpu time 0.01 seconds
22: LINE and COLUMN cannot be determined.
NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 22-322: Syntax error, expecting one of the following: a name, ;, -, :, DECENDING, DESCENDING,
DESENDING, _ALL_, _CHARACTER_, _CHAR_, _NUMERIC_.
200: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where
the error has occurred.
ERROR 200-322: The symbol is not recognized and will be ignored.
WARNING: Apparent symbolic reference I_COMPANYNAME not resolved.
ERROR: Variable _PREVIOUSNAME_ not found.
ERROR: Variable I_COMPANYNAME not found.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
NOTE: Line generated by the invoked macro "PREVIOUSNAME".
5 Step2.Patstat_gb_hrm (in=ina) Sa_step2.House_company_base (in=inb) ; by
5 ! _PreviousName_&i_CompanyName; if ina and inb then output STEP6.CompanyHouse_Patstat&i; else
-
22
200
WARNING: Apparent symbolic reference I_COMPANYNAME not resolved.
ERROR 22-322: Syntax error, expecting one of the following: a name, -, :, ;, DESCENDING, GROUPFORMAT,
NOTSORTED, _ALL_, _CHARACTER_, _CHAR_, _NUMERIC_.
ERROR 200-322: The symbol is not recognized and will be ignored.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set STEP6.COMPANYHOUSE_PATSTAT1 may be incomplete. When this step was stopped
there were 0 observations and 61 variables.
WARNING: The data set STEP4.PATSTAT_GB_HRM1 may be incomplete. When this step was stopped there were
0 observations and 61 variables.
WARNING: The data set STEP3.COMPANYHOUSE1 may be incomplete. When this step was stopped there were 0
observations and 61 variables.
NOTE: DATA statement used (Total process time):
real time 0.20 seconds
cpu time 0.01 seconds
could you please give me some suggestions about it?
thanks in advance
Hello,
You must have the run inside the do loop, and also mention &i. (not the period at end)
options symbolgen mprint mlogic;
%macro PreviousName;
%do i=1 %to 10;
data Sa_step2.House_company_base;
set Sa_step2.House_company_base;
_PreviousName_&i._CompanyName =upcase(_PreviousName_&i._CompanyName);
call scan(_PreviousName_&i._CompanyName, -1, position, length);
if scan(_PreviousName_&i._CompanyName,-1) in ('LTD' ,'LTD.','LIMITED.') then substr(_PreviousName_&i._CompanyName,position)='LIMITED';
drop position length;
run;
%end;
%mend PreviousName;
%PreviousName;
Hello,
You must have the run inside the do loop, and also mention &i. (not the period at end)
options symbolgen mprint mlogic;
%macro PreviousName;
%do i=1 %to 10;
data Sa_step2.House_company_base;
set Sa_step2.House_company_base;
_PreviousName_&i._CompanyName =upcase(_PreviousName_&i._CompanyName);
call scan(_PreviousName_&i._CompanyName, -1, position, length);
if scan(_PreviousName_&i._CompanyName,-1) in ('LTD' ,'LTD.','LIMITED.') then substr(_PreviousName_&i._CompanyName,position)='LIMITED';
drop position length;
run;
%end;
%mend PreviousName;
%PreviousName;
Learning to use Base SAS - the programming language - and its constructs will improve your code making it quicker, less resource hungry, more robust, and simpler to read.
Example:
I have a set of variables, all of which I need to do some processing on.
Answer:
Use arrays of variables.
As you have not shown test data input, or required output I cannot code this for you.
As to your issue, it comes about due to bad coding practices. You have not finished the macro variable invocation correctly using a decimal place, therefore the compiler is looking for a macro variable called: i_CompanyName to resolve to a value.
Follow good programming practice and always finish macro variables with a decimal place to avoid such issues!
_PreviousName_&i._CompanyName
Do note however that use of arrays, and structuring your data in a usable method would remove the need for most of this code in the first place.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.