Hi all,
I have an Excel file which contains columns called : RAF 2016, RAF 2017, ..., RAF 2021
In the next year, we will have an additional column RAF 2022
Then in 2023, we will have RAF 2023 etc
After importing the Excel file, i create a macro variable which contains the last_year in the Excel file (for example if the last column is RAF 2022, Last_year = 2022)
In my program, i need to select the columns name dynamically.
So, i prepare the name of the column using this : %let col1 = %str(%')RAF &i.%str(%'n); // i want here to prepare something like that : 'RAF 2016'n , 'RAF 2017'n ... to be able to use it as column name in the select
%macro m2;
%do i=2016 %to &Last_year.;
%let col1 = %str(%')RAF &i.%str(%'n);
PROC SQL;
CREATE TABLE TOUT_SOLDE_3 AS
SELECT t.*,
t1.&col1.
FROM TOUT_SOLDE_3 t
LEFT JOIN TOUT_SOLDE_2 t1 ON t.UR = t1.UR and t.UE = t1.UE;
QUIT;
%end;
%mend;
But i have an error message :
ERROR 22-322: Syntax error, expecting one of the following: a name, *.
ERROR 76-322: Syntax error, statement will be ignored.
I don't understand where is the issue.
Can you help please ?
To debug macro issues, use
options mprint;
before you run the macro, then run the macro again and look at the log. If you can't figure it out, please show us the entire log for the run of this macro (not selected parts of the log, not error messages detached from the code as you have done). Copy the log as text and paste it into the window that appears when you click on the </> icon. DO NOT SKIP THIS STEP.
Also, you make your programming harder by having calendar information as a variable name. Almost all programming will be easier if the year is a variable named YEAR in a long data set, rather then have these names for variables in a wide data set. If I were you, I would re-arrange the data set into a long data set.
Hint: What is your setting for the system option VALIDVARNAME?
You do not say how you are reading the Excel to begin with but you might simplify your life by not allowing those tedious name literals as you can have all sorts of fun stuff like guessing one space but there are actually 2 (or more) present.
Immediately transpose such data after the import. Use
var raf:;
to automatically catch all variables.
Not sure what you want to do but it looks to me like you have the %DO loop in the wrong place. Also SAS has a function for making name literals, let it do that work for you.
%macro m2;
%local i name ;
PROC SQL;
CREATE TABLE TOUT_SOLDE_3 AS
SELECT t.*
%do i=2016 %to &Last_year.;
%let name = %sysfunc(nliteral(RAF &i.));
, t1.&name
%end;
FROM TOUT_SOLDE_3 t
LEFT JOIN TOUT_SOLDE_2 t1
ON t.UR = t1.UR and t.UE = t1.UE
;
QUIT;
%mend;
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 25. 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.