You are getting closer. Now the issue is that no variables matched your criteria but you still tried to run the RENAME statement. The error message you got was because since no variables met the criteria PROC SQL never created the macro variable so when you referenced it it was not there.
But fixing just that will not fix the code, it will just change the error message.
For example this is the error message you get when you try to use a RENAME statement in PROC DATASETS with no actual pairs of names.
149 proc datasets lib=work nolist;
150 modify want;
151 rename ;
-
22
76
NOTE: Enter RUN; to continue or QUIT; to end the procedure.
ERROR 22-322: Expecting a name.
ERROR 76-322: Syntax error, statement will be ignored.
152 run;
NOTE: Statements not processed because of errors noted above.
153 quit;
So you need to prevent the PROC DATASETS step from running when no variables where found.
You can do that by testing the automatic macro variable SQLOBS.
%if &sqlobs %then %do;
proc datasets library=work nodetails nolist;
modify final_&i;
rename &rename_list;
run; quit;
%end;
But you do realize the rest of your macro is just gibberish also.
Let's look at the beginning few steps:
proc contents data=b1 out=list(keep=name) noprint;
data list1; set list(keep=name); if name in ('Name','Country','CISOC','ID','Status','Legal', 'Entity','Code','Type'); keep name;
data list_&i; set list; if substr(name, length(name)-3) = "&i" then output; keep name;
data l_&i; set list1 list_&i;
So the first thing one notices is the lack of formatting and missing RUN statements to end the steps.
First you use PROC CONTENTS to get the list of variable names from the data B1. (Notice that B1 is not listed as an INPUT to this macro so where did it come from?)
Then you keep only the NAME variable from the output of PROC CONTENTS and only the observations that match a short list of about 9 names. And only when the exact mixed case letters were used to define the variable's name. So you won't find Name if it was created as NAME or name or NAme etc.
You then make another subset of the names, this time just the ones that end with _ and the value of the macro variable I.
Then you put them back together.
What is the goal here? Is to somehow move the first 9 variables up in the sort order?
Then you make a copy of this mystery B1 dataset, but you only keep the variable named L_ followed by the value of macro variable I.
Are you sure that such a variable exists?
Then comes the logic that was causing the error. Basically you are checking this new copy of B1 that you called FINAL_&i to see if it contains any variables that end in _&i. But since you just created it to only have L_&i you should already know the answer.
I suspect that the real goal is to make a subset of this mystery B1 dataset that only has 9 key variables and any other variables that have a year suffix. With the added wrinkle of renaming them to remove the year suffix.
I am not sure why you are looping the year from 2008 to 2022, so perhaps you want to convert the data into multiple observations, one for each year? If so then add a YEAR variable so you can distinguish the duplicated rows.
So try something like this:
%macro ex(dsin,dsout);
%local i keynames keep_list rename_list ;
%let keynames=Name Country CISOC ID Status Legal Entity Code Type;
%do i=2008 %to 2022;
proc contents data=&dsin out=list(keep=name) noprint;
run;
proc sql noprint;
select nliteral(name)
, catx('=', nliteral(name), substr(name, 1, length(name)-5))
into :keep_list separated by ' '
, :rename_list separated by ' '
from list
where upcase(trim(name)) like "%_&i"
;
quit;
data subset_&i;
year = &i ;
set b1(keep=&keynames &keep_list);
%if (&sqlobs) %then %do;
rename &rename_list;
%end
run;
%end;
data &dsout;
set subset_2008-subset_2022;
run;
%mend;
proc import datafile='/data/bvd/export.xlsx' out=b1 dbms=xlsx replace;
%ex(b1,home.final);
Click image to register for webinar
