Hello all,
I have an ODBC connection to a housing DB, one field I need has a '#' pound sign or hashtag in the field name, (no I do not know the person that designed it) they could use a good shaking if it was legal haha... 😎
proc sql;
create table testing_kmj as
select * from my_lib.my_table
where 1 = 2
;quit;
proc contents data= testing_kmj ORDER=VARNUM;
run;
Variables in Creation Order
# Variable Type Len Format Informat Label
...
6 ID Char 250 $250. $250. ID
7 RM# Char 250 $250. $250. RM#
8 AssetType Char 250 $250. $250. AssetType
9 FacilityFullName Char 500 $500. $500. FacilityFullName
Anyway, I am trying to rename it and format it to: $19. the max size I have found in all year's worth of the data set to date.
proc sql;
create table my_new_table as
select distinct
ID AS emplid format = $11. Informat=$11. length 11 label='emplid',
Patron_SK_FK format = 11. Informat=11. length 8 label='Patron_SK_FK',
EffectiveDate,
EndDate,
/* trim(RM#) as rm_num format = $19. Informat=$19. length 19 label='rm_num',*/
RM# as testing
from my_lib.my_table
;quit;
I get this log error:
13104 RM# as testing,
-
22
200
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, (, *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?, AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, LEN,
LENGTH, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.
ERROR 200-322: The symbol is not recognized and will be ignored.
I can proc print the data its all there (when I actually read in the data) as I need, it's just getting rid of the hard to use column name I want in this case. A data step would be fine too.
I have played around a bit with:
options validvarname = any;
options VALIDMEMNAME = extend;
These options & v7 are not helping me in this case. PS if it helps I am using Windows10, and SAS 9.4 M3
TIA -Keith
If you re-import the data with the setting VALIDVARNAME=V7 it will convert RM# to RM_ which is a valid SAS name.
Once you set the VALIDVARNAME=ANY option you still need to use name literals when referencing names that break the normal rules.
'RM#'n
If you re-import the data with the setting VALIDVARNAME=V7 it will convert RM# to RM_ which is a valid SAS name.
SASKiwi,
"If you re-import the data with the setting VALIDVARNAME=V7 it will convert RM# to RM_ which is a valid SAS name."
I miss understood what this option was doing. This is the easiest fix, what I missed was that this option 'auto' changes in my case RM# to RM_ I thought it was making RM# a legitimate name, and then I would have to rename it. d'oh. Thank you. -KJ
Also try another libname option (I don't remember exactly )
libname my_lib db2 dsn=xxx preserve_col_name=yes ;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.