What's wrong with this SAS macro code?

[d6k5d3]

I have 2 files EU_CB_1 and EU_CB_2 in xlsx format. I want to import, append and format these 2 files using macro. I fail to understand why I get errors. Would you please help me identify the error?

%macro impcol (reg,typ,start,count);
%do i=&start %to &count;
proc import datafile='C:\Users\...\&reg_&typ_&i.xlsx'
dbms=xlsx replace out=&reg_&typ_&i;
getnames=yes;
run;

data &reg_&typ;
set &reg_&typ_&i;
drop Date_Time;
if Date=' ' and Event=' ' then delete;
rename Country_Region=Region;
rename Surv_M_=Surv_M;
rename Surv_A_=Surv_A;
rename Std_Dev=SD;
rename Last_Rev_=Last_Rev;
run;
%end;
%mend impcol;
%impcol (EU,CB,1,2);

I get the following errors:

2137 %macro impcol (reg,typ,start,count);
2138 %do i=&start %to &count;
2139 proc import datafile='C:\Users\Deepan\OneDrive\Brock Studies\Fall 2018\Thesis\Data\WIP\Eco
2139! Data\Common\&reg_&typ_&i.xlsx'
2140 dbms=xlsx replace out=&reg_&typ_&i;
2141 getnames=yes;
2142 run;
2143
2144 data &reg_&typ;
2145 set &reg_&typ_&i;
2146 drop Date_Time;
2147 if Date=' ' and Event=' ' then delete;
2148 rename Country_Region=Region;
2149 rename Surv_M_=Surv_M;
2150 rename Surv_A_=Surv_A;
2151 rename Std_Dev=SD;
2152 rename Last_Rev_=Last_Rev;
2153 run;
2154 %end;
2155 %mend impcol;
2156 %impcol (EU,CB,1,2);
NOTE: Line generated by the invoked macro "IMPCOL".
1 proc import datafile='C:\Users\Deepan\OneDrive\Brock Studies\Fall 2018\Thesis\Data\WIP\Eco
1 ! Data\Common\&reg_&typ_&i.xlsx' dbms=xlsx replace out=&reg_&typ_&i; getnames=yes; run; data
-
22
WARNING: Apparent symbolic reference REG_ not resolved.
WARNING: Apparent symbolic reference TYP_ not resolved.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

NOTE: The SAS System stopped processing this step because of errors.
WARNING: Apparent symbolic reference REG_ not resolved.
NOTE: Line generated by the invoked macro "IMPCOL".
1 &reg_&typ_&i; getnames=yes; run;

-

200

ERROR 22-322: Expecting a name.

ERROR 200-322: The symbol is not recognized and will be ignored.

NOTE: Line generated by the invoked macro "IMPCOL".
1 data &reg_&typ; set

-

22

200
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, /, ;,
_DATA_, _LAST_, _NULL_.

ERROR 200-322: The symbol is not recognized and will be ignored.

1 ! data &reg_&typ; set
1 ! &reg_&typ_&i; drop Date_Time; if Date=' ' and Event=' '
-
22
WARNING: Apparent symbolic reference REG_ not resolved.
WARNING: Apparent symbolic reference TYP_ not resolved.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, ;, CUROBS,
END, INDSNAME, KEY, KEYRESET, KEYS, NOBS, OPEN, POINT, _DATA_, _LAST_, _NULL_.

NOTE: Line generated by the invoked macro "IMPCOL".
1 &reg_&typ_&i; drop Date_Time; if Date=' ' and Event=' '

-
-

200
22

200
ERROR: File WORK.REG_.DATA does not exist.
ERROR: File WORK.TYP_1.DATA does not exist.

ERROR 200-322: The symbol is not recognized and will be ignored.

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, -, :, ;,
CUROBS, END, INDSNAME, KEY, KEYRESET, KEYS, NOBS, OPEN, POINT, _DATA_, _LAST_,
_NULL_.

WARNING: The variable Date_Time in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable Country_Region in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable Surv_M_ in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable Surv_A_ in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable Std_Dev in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable Last_Rev_ in the DROP, KEEP, or RENAME list has never been referenced.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.REG_CB may be incomplete. When this step was stopped there were 0
observations and 2 variables.
WARNING: Data set WORK.REG_CB was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.05 seconds
cpu time 0.04 seconds

NOTE: Line generated by the invoked macro "IMPCOL".
4 proc import datafile='C:\Users\Deepan\OneDrive\Brock Studies\Fall 2018\Thesis\Data\WIP\Eco
4 ! Data\Common\&reg_&typ_&i.xlsx' dbms=xlsx replace out=&reg_&typ_&i; getnames=yes; run; data
-
22
WARNING: Apparent symbolic reference REG_ not resolved.
WARNING: Apparent symbolic reference TYP_ not resolved.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

NOTE: The SAS System stopped processing this step because of errors.
WARNING: Apparent symbolic reference REG_ not resolved.
NOTE: Line generated by the invoked macro "IMPCOL".
4 &reg_&typ_&i; getnames=yes; run;

-

200

ERROR 22-322: Expecting a name.

ERROR 200-322: The symbol is not recognized and will be ignored.

NOTE: Line generated by the invoked macro "IMPCOL".
4 data &reg_&typ; set

-

22

200
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, /, ;,
_DATA_, _LAST_, _NULL_.

ERROR 200-322: The symbol is not recognized and will be ignored.

4 ! data &reg_&typ; set
4 ! &reg_&typ_&i; drop Date_Time; if Date=' ' and Event=' '
-
22
WARNING: Apparent symbolic reference REG_ not resolved.
WARNING: Apparent symbolic reference TYP_ not resolved.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, ;, CUROBS,
END, INDSNAME, KEY, KEYRESET, KEYS, NOBS, OPEN, POINT, _DATA_, _LAST_, _NULL_.

NOTE: Line generated by the invoked macro "IMPCOL".
4 &reg_&typ_&i; drop Date_Time; if Date=' ' and Event=' '

-
-

200
22

200
ERROR: File WORK.REG_.DATA does not exist.
ERROR: File WORK.TYP_2.DATA does not exist.

ERROR 200-322: The symbol is not recognized and will be ignored.

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, -, :, ;,
CUROBS, END, INDSNAME, KEY, KEYRESET, KEYS, NOBS, OPEN, POINT, _DATA_, _LAST_,
_NULL_.

WARNING: The variable Date_Time in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable Country_Region in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable Surv_M_ in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable Surv_A_ in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable Std_Dev in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable Last_Rev_ in the DROP, KEEP, or RENAME list has never been referenced.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.REG_CB may be incomplete. When this step was stopped there were 0
observations and 2 variables.
WARNING: Data set WORK.REG_CB was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.04 seconds
cpu time 0.03 seconds

[Kurt_Bremser · Accepted solution]

Make it a habit to always end macro variable references with a dot, and use double quotes for strings where macro variables need to be resolved:

"C:\Users\...\&reg._&typ._&i..xlsx"

Without the dot, the macro preprocessor tries to find macro variable reg_ instead of just reg. Note the double dot: one terminates the reference to &i, the other one stays in the string and goes into the filename.

Do the same throughout your macro wherever you reference the macro variables.

[d6k5d3]

@Kurt_Bremser, thank you! I will never forget the pieces of advice.

Regards.

[Kurt_Bremser]

You can find this advice as Maxim #48 in https://communities.sas.com/t5/SAS-Communities-Library/Maxims-of-Maximally-Efficient-SAS-Programmers...