SAS - Using variable in table name

Question

Relatively new to SAS but I have come across an unusual issue. I use some proc sql statements to dynamically create a variable based on the latest month. The tables I want to retrieve from have a month name in the middle of them and I want to loop through. E.g. LIBRARY.TABLE_JAN17_ALL LIBRARY.TABLE_DEC16_ALL etc.

Example Code:

 DATA qtrMonth;
 INPUT vDay vMonth vMonthName $;
 DATALINES;
31 01 JAN
28 02 FEB
31 03 MAR
30 04 APR
31 05 MAY
30 06 JUN
31 07 JUL
31 08 AUG
30 09 SEP
31 10 OCT
30 11 NOV
31 12 DEC
;

DATA year;
INPUT vYear ;
DATALINES;
14
15
16
17
;
run;

/* Next step is to do a cartesian join to populate
   all possible Month & Year combos in the dataset  */
PROC SQL;
    create table popCalendar as
    SELECT 
    mdy(a.vMonth,a.vDay, b.vYear) as MyDate format  DATE9.
    ,CAT(TRIM(a.vMonthName), b.vYear) as MonthName
    FROM qtrMonth a
    CROSS JOIN year b
    ;
quit;

PROC SQL;
create table tmpMax as 
Select Max(MyDate) as MaxDate
FROM popCalendar
WHERE MyDate < today();

/*select max monthName into vMonth variable */
SELECT trim(MonthName) into :vMonth
FROM popCalendar a
inner join tmpMAX b on a.MyDate = b.MaxDate;
quit;

/*Select from table using variable*/
proc sql;
create table abc as 
select * from LIBRARY.TABLE_&vMonth._ALL;

Previously, I used this logic for table names where the ending was the MonthName i.e. LIBRARY_TABLEACC_JAN17 -> LIBRARY.TABLEACC_&vMonth. I had no issues with this but now that the variable is in the middle of the table name I get an error. ERROR: File LIBRARY.TABLE_JAN17.DATA does not exist.

If I use the following code it works fine, but I want to loop through 120 months of data so don't want to individually name each month & year.

%Let vMonth2 = JAN17;

proc sql;
create table abc as 
select * from LIBRARY.TABLE_&vMonth2._ALL ;

Can anyone advise as to what the issue is?

Thanks.

Tom Tom · Accepted Answer · 2017-02-23T15:27:58

There are two possible issues. The simplest one is that you inserted blanks into your macro variable. To test that theory just display the value with something around it.

%put |&vmonth|;

Your SQL code should use the TRIMMED keyword (of if you are running an old version of SAS use the SEPARATED BY keyword instead) to force SAS to remove the blanks when making the macro variable.

SELECT MonthName into :vMonth trimmed

The other possibility is that the SAS compiler is confused by seeing a macro reference in the middle of a object name and is mistakenly treating it as two or more token even if there are no actual embedded blanks in the value. But this normally only happens when the macro variable value has been macro quoted. If you see that the value does NOT have actual blanks in it then try using the %unquote() macro function.

select * from LIBRARY.%unquote(TABLE_&vMonth._ALL);

SAS - Using variable in table name

1 Answers