Dealing with hyphen in field name in SAS EG

[J_Yetman]

I'm trying to work with data where there is a field named E-mail_Address.  The hyphen in the field name is causing SAS to look at the field as if it is two different fields, as I get an error telling me that 'E' and 'mail_Address' are invalid field names.

Neither encasing the field in the text qualifier (such as 'E-mail_Address'n) nor using options validvarname=ANY resolves the issue.

[ballardw]

1) show an example value, need not be real but similar to the one you are using

2) post the code or clearly describe what operation you are doing to the variable.

There are a number of functions that could be doing that and how to fix requires more details, likely telling the function optionally which delimiters to use explicitly such as period and @ instead using default word delimiters which will include many punctuation characters.

[J_Yetman]

Posting code and log here.  Please note the issue does not appear to be with the data within the field - it appears to be with the field name itself.

1 The SAS System 09:33 Monday, March 30, 2020

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program (4)';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='\\EFS-1SMB001\team9\MAO\Workspace\Campaign Team\Josh\Latimer_Detail.egp';
6 %LET _CLIENTPROJECTPATHHOST='PCVPTWX6OP08348';
7 %LET _CLIENTPROJECTNAME='Latimer_Detail.egp';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=PNG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 FILENAME EGSR TEMP;
15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16 STYLE=HtmlBlue
17 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
18 NOGTITLE
19 NOGFOOTNOTE
20 GPATH=&sasworklocation
21 ENCODING=UTF8
22 options(rolap="on")
23 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24
25 GOPTIONS ACCESSIBLE;
26 options validvarname=any;
27
28 proc sql;
29
30 create table work.com_base as
31 select distinct a.web_id as pid,
32 a.email_address as email,
32 !
33 b.ERM_PN_Name as PN_customer_name,
34 b.ERM_RPN_Name as RPN_customer_name,
35 b.ERM_Contact_Title as job_title,
36 b.ERM_Contact_First_Name as first_name,
37 b.ERM_Contact_Middle_Name as middle_name,
38 b.ERM_Contact_Last_Name as last_name,
39 c.'ERM_PN_Customer/Prospect_Status'n as status,
40 d.ERM_PAO_Segment as line_of_business,
41 case when d.ERM_Managed_Client_Flag = 'Yes' then 'Y' else 'N' end as managed_flag,
42 case when d.ERM_Managed_Client_Flag = 'Yes' then d.ERM_PAO_Name_Last_First else 'N/A' end as Relationship_Manager,
43 b.ERM_PN as profile_number,
44 b.ERM_RPN as relationship_profile_number
45 from work.mailfile a
46 left join COMMDAST.CDS_ts_ERMD_Contact_Details b
47 on a.Email_Address = upcase(b.'ERM_Contact_E-mail'n)
48 left join COMMDAST.CDS_ts_ERMD_Profile_Details c
49 on b.ERM_PN = c.ERM_PN
50 left join COMMDAST.CDS_ts_ERMD_Relationship_Details d
51 on c.ERM_RPN = d.ERM_RPN
52 where a.creative_type = 'COM'
53 ;
ERROR: CLI describe error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Invalid column name 'ERM_Contact_E'. :
[SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Invalid column name 'mail'. : [SAS][ODBC SQL Server Wire
Protocol driver][Microsoft SQL Server]The batch could not be analyzed because of compile errors.
2 The SAS System 09:33 Monday, March 30, 2020

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
54
55 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.40 seconds
cpu time 0.34 seconds

56

57 proc sql;
58
59 create table work.com_list as
60 select upcase('ERM_Contact_E-mail'n) as Email_Address,
61 ERM_PN_Name as PN_customer_name,
62 ERM_RPN_Name as RPN_customer_name,
63 ERM_Contact_Title as job_title,
64 ERM_Contact_First_Name as first_name,
65 ERM_Contact_Middle_Name as middle_name,
66 ERM_Contact_Last_Name as last_name,
67 ERM_PN as profile_number,
68 ERM_RPN as relationship_profile_number
69 from COMMDAST.CDS_ts_ERMD_Contact_Details
70 where upcase('ERM_Contact_E-mail'n) in (select Email_Address from work.mailfile where creative_type = 'COM');
ERROR: CLI describe error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Invalid column name 'ERM_Contact_E'. :
[SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Invalid column name 'mail'. : [SAS][ODBC SQL Server Wire
Protocol driver][Microsoft SQL Server]The batch could not be analyzed because of compile errors.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
71
72 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.05 seconds
cpu time 0.05 seconds

73
74 GOPTIONS NOACCESSIBLE;
75 %LET _CLIENTTASKLABEL=;
76 %LET _CLIENTPROCESSFLOWNAME=;
77 %LET _CLIENTPROJECTPATH=;
78 %LET _CLIENTPROJECTPATHHOST=;
79 %LET _CLIENTPROJECTNAME=;
80 %LET _SASPROGRAMFILE=;
81 %LET _SASPROGRAMFILEHOST=;
82
83 ;*';*";*/;quit;run;
84 ODS _ALL_ CLOSE;
85
86
87 QUIT; RUN;
88

[Tom]

Looks like an SQL Server issue. What does PROC CONTENTS show as the names of the variables in the table you are referencing from SAS as COMMDAST.CDS_ts_ERMD_Contact_Details.

[TomKari]

I was sure it would be fine, and I was right...

data abc;
length 'E-mail_Address'n $5;
'E-mail_Address'n = "def"; output;
'E-mail_Address'n = "ghi"; output;
run;

24         
25         data abc;
26         length 'E-mail_Address'n $5;
27         'E-mail_Address'n = "def"; output;
28         'E-mail_Address'n = "ghi"; output;
29         run;

NOTE: The data set WORK.ABC has 2 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds