topic Re: Help rename Colum with pound sign or hashtag in name of Colum? in SAS Programming

Help rename Colum with pound sign or hashtag in name of Colum?

kjohnsonm — Mon, 02 Dec 2019 19:22:22 GMT

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

Re: Help rename Colum with pound sign or hashtag in name of Colum?

Tom — Mon, 02 Dec 2019 19:54:22 GMT

Once you set the VALIDVARNAME=ANY option you still need to use name literals when referencing names that break the normal rules.

'RM#'n

Re: Help rename Colum with pound sign or hashtag in name of Colum?

SASKiwi — Mon, 02 Dec 2019 20:41:34 GMT

If you re-import the data with the setting VALIDVARNAME=V7 it will convert RM# to RM_ which is a valid SAS name.

Re: Help rename Colum with pound sign or hashtag in name of Colum?

Ksharp — Tue, 03 Dec 2019 12:10:52 GMT

Also try another libname option (I don't remember exactly )

libname my_lib db2 dsn=xxx  preserve_col_name=yes ;

Re: Help rename Colum with pound sign or hashtag in name of Colum?

kjohnsonm — Tue, 03 Dec 2019 15:26:17 GMT

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