PROC SQL : USE MACRO VARIABLES AS COLUMNS NAMES

[marzouso]

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 ?

[PaigeMiller]

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.

[ballardw]

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.

[Kurt_Bremser]

Immediately transpose such data after the import. Use

var raf:;

to automatically catch all variables.

[Tom]

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;