CODEWITHSUNDEEP
sas
Ben
Ben

Reputation: 39

How to refer to a variable name that is a number in sas sql

I've got following issue:

I want to use a variable column name in sas sql.

Get this error message:

 t1.201912
                   
ERROR 22-322: Syntax error, expecting one of the following: ein Name, *.

Somehow my sql statement doesn't recognize these columns 201912 and 202006.

Can someone help me out?

This is my code:

%let PRV_MONTH = %sysfunc(intnx(month,%sysfunc(today()), -1,same),YYMMN.); 
%let End_PRV_YEAR = %sysfunc(intnx(year,%sysfunc(today()), -1,e),YYMMN.);

PROC SQL;   
CREATE TABLE WORK.RESULT AS 
SELECT t2.DIM_TIME_ID, 
      t1.AGENCY,
      Sum(t2.STACK) as OWN_STACK 
      
  FROM xy.SALES t1, xy.STACK t2
  WHERE (t1.DIM_SALES_ID = t2.DIM_SALES_ID AND t2.DIM_TIME_ID IN (&End_PRV_YEAR,&PRV_MONTH))
  GROUP BY t2.DIM_TIME_ID,t1.AGENCY
  ORDER BY t1.AGENCY ASC; 
QUIT;



PROC TRANSPOSE DATA=WORK.RESULT OUT=horizontal;
BY AGENCY;
ID DIM_TIME_ID;

QUIT;
PROC SQL;

CREATE TABLE WORK.QUERY_FOR_HORIZONTAL AS 
SELECT t2.AREA, 
      t2.DISTRICT, 
      t1.AGENCY,
      t1.&End_PRV_YEAR, 
      t1.&PRV_MONTH
  FROM WORK.HORIZONTAL t1 INNER JOIN xy.SALES t2 ON (t1.AGENCY = t2.AGENCY);
QUIT;

Upvotes: 2

Views: 1124

Answers (1)

Richard
Richard

Reputation: 27498

Depending on the actual data set you may need to use

The viewer is showing a column label 201912 and the actual column name is _201912:

t1._201912

or the actual column name is 201912 and you need to use name literal syntax:

t1.'201912'n

Upvotes: 2

Related Questions