topic Numbers as variable/column names in SAS Studio in SAS Programming

Numbers as variable/column names in SAS Studio

emptyhead — Fri, 28 Jun 2024 10:04:32 GMT

Hello everyone, I really need some advise. I have table whose column names are numbers and I can't change this setting. Now I am trying to find a solution how to union this table with others in SAS Studio. Here's what I got so far:

options msglevel=i;
options noquotelenmax;
options validvarname=ANY; /* for variables*/  

filename genFuncs FILESRVC folderpath='/department/lib_name' filename='general_functions_v2.sas';
%include genFuncs / source;

proc cas;
  %casFunctions;
  
  outCaslibName="lib_name";
  casTableName="table_union";
  tmpTableName = casTableName || "_" || rand("Integer", 111111, 999999);

  createTableString="create table lib_name." || tmpTableName ||" {options replace=true} as (

 select v.month from lib_name.normal_table v
 union all
 select t.'198'n from lib_name.uqly_table t
  )";

and I can't convince SAS, that '198'n is the month column name.
error

Re: Numbers as variable/column names in SAS Studio

Tom — Fri, 28 Jun 2024 11:43:18 GMT

I do not use CAS language, but it looks to me like you are creating a character variable that happens to contain what looks like SQL syntax. If it is just a string then the content shouldn't matter. So somewhere under the hood the string must have been used by some SQL process. Perhaps that SQL process would rather that you use ANSI style double quotes around invalid names?

Try:

createTableString
   ="create table lib_name." || tmpTableName
  ||" {options replace=true} as ("
  ||"select v.month from lib_name.normal_table v "
  ||" union all "
  ||"select t.""198"" from lib_name.uqly_table t"
  ||")"
;

Re: Numbers as variable/column names in SAS Studio

Kurt_Bremser — Fri, 28 Jun 2024 11:55:24 GMT

Fix the process that creates the ugly table, so you don't have to deal with those stupid name literals.

Re: Numbers as variable/column names in SAS Studio

emptyhead — Fri, 28 Jun 2024 12:14:38 GMT

Thousand thanks, it works!