topic Re: Naming SQL columns as dates in format DDMMMYYY in New SAS User

Naming SQL columns as dates in format DDMMMYYY

veneling — Tue, 03 Sep 2019 08:45:29 GMT

Hello,

I have a proc sql query that compares two sets of forecasts and would like the names of two columns to be dates in format "DDMMMYYYY" (like for instance 01SEP2019) but SAS complains this is not valid.

This is the error I receive

49          25AUG2019
            __
            22
            76
ERROR 22-322: Expecting a name.

I guess due to the rules that apply for naming - http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000998953.htm

Is there a workaround to achieve what I want?

This is my code

%let oldForecastDate = 25AUG2019;
%let newForecastDate = 01SEP2019;

PROC SQL;
CREATE TABLE WORK.MAT_MASTER_DETAILS AS 
SELECT distinct
  Matnum, 
  MatDesc,
  Segment as Category
FROM DATA.MATERIAL_MASTER
WHERE Segment IN (12, 46) ;
QUIT;


PROC SQL;
CREATE TABLE WORK.fcst_diff AS 
  SELECT 
  A.matnum,
  A.MatDesc,
  A.Category,
  A.fc_mon,
  A.quantity as &oldForecastDate,
  B.quantity as &newForecastDate,
  B.quantity - A.quantity as Diff,
  put((B.quantity - A.quantity)/B.quantity, percentn8.2) as Pers_Diff
FROM
  (SELECT A1.matnum, A2.MatDesc, A2.Category, A1.fc_mon, A1.quantity 
  FROM DATA.FORECAST as A1
  INNER JOIN
  (
  SELECT Matnum, MatDesc, Category FROM 
  WORK.HOME_MAT_MASTER_DETAILS
  ) AS A2 
  ON A1.matnum = A2.Matnum 
  AND date = "&oldForecastDate"d
  ) AS A
,
  (SELECT B1.matnum, B2.MatDesc, B2.Category, B1.fc_mon, B1.quantity
  FROM DATA.FORECAST as B1
  INNER JOIN 
  (
  SELECT Matnum, MatDesc, Category FROM 
  WORK.HOME_MAT_MASTER_DETAILS
  ) as B2 
  ON B1.matnum = B2.Matnum
  AND date = "&newForecastDate"d
  ) AS B
  WHERE A.matnum = B.matnum AND A.fc_mon = B.fc_mon
;
QUIT;

I tried using validvarname=any but this didn't help.

Re: Naming SQL columns as dates in format DDMMMYYY

PeterClemmensen — Tue, 03 Sep 2019 08:51:42 GMT

@veneling hi and welcome to the SAS Communities 🙂

The best workaround I can think of is to find another name for your column. It is rarely (never) a good idea to structure data like this.

If you insist on structuring your data like this though, a simple underscore in from of your variable names will save you.

Re: Naming SQL columns as dates in format DDMMMYYY

Kurt_Bremser — Tue, 03 Sep 2019 08:57:24 GMT

Very simple. Use valid SAS names, e.g. start the names with an underline.

This can be achieved simply by doing

A.quantity as quantity_&oldForecastDate,

and you even get more meaningful variable names on top.

Using a typical date value as a variable name would be suboptimal (I'm deliberately polite here) anyway, as it opens you up for all kinds of confusion in your programs.

Re: Naming SQL columns as dates in format DDMMMYYY

veneling — Tue, 03 Sep 2019 09:10:55 GMT

Okay, perhaps I will go to this solution even though the description becomes a bit overloaded and I want it as neat as possible.

The only reason for trying to have date as column name is that after get the report I transpose it and the comparison is distinctively visible for the people that read it. Otherwise I wouldn't do that.