topic Re: SAS Entreprise Guide and long variable names in SAS Enterprise Guide

SAS Entreprise Guide and long variable names

SallyS — Wed, 21 Oct 2015 18:30:43 GMT

I am having problems trying to schedule a SAS program with EG because of long variable names in a table I have no control over.

This table specifically has 3 variables that have names with the same 32 first characters. The names are up to 40 characters long!

SAS handles this for me but EG does not seem to do that. Any ideas?

Re: SAS Entreprise Guide and long variable names

SASKiwi — Wed, 21 Oct 2015 18:37:31 GMT

When you say EG does not handle long variable names what do you mean? Please provide more details.

When SAS truncates long variable names it will rename them by adding a digit on the end if they are no longer unique. Is this the behaviour you are seeing?

Re: SAS Entreprise Guide and long variable names

LinusH — Thu, 22 Oct 2015 09:34:40 GMT

Are these three variables stored in an external RDBMS that allows longer column names? If yes, this should be handled when assigning the libanme.

Or are they created as a step in your process flow? Pls. give us an example (code/log).

Re: SAS Entreprise Guide and long variable names

SallyS — Thu, 22 Oct 2015 11:04:28 GMT

I am adding the coding I am using to try and access this data (note that I am not including all variables but am including the long named variables).

DATA ACCT_DETAIL; SET MyLibrary.Thedata (rename=(
AccountingDetailID=acct_id
AccountingDetail_AccountingNumbe=acct_num
AccountingDetail_ClaimReceivedDa=claimreceiveddate
AccountingDetail_ClaimStatus=claimstatus
AccountingDetail_ExtendedClaimSt=extclaimstatus_id
AccountingDetail_ExtendedClaimS0=extclaimstatus
AccountingDetail_ExtendedClaimS1=extclaimstatus_mrp
LastUpdate=last_update
)); run;

The variables that are in bold are providing the problem.

When I look at the data through SAS these are the names I see (already truncated and made unique by SAS).

The following is from the log:

ERROR: Variable AccountingDetail_ExtendedClaimS0 is not on file MyLibrary.Thedata.
ERROR: Variable AccountingDetail_ExtendedClaimS1 is not on file MyLibrary.Thedata.
ERROR: Invalid DROP, KEEP, or RENAME option on file MyLibrary.Thedata.

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.ACCT_DETAIL may be incomplete. When this step was stopped there
were 0 observations and 0 variables.
WARNING: Data set WORK.ACCT_DETAIL was not replaced because this step was stopped.

Re: SAS Entreprise Guide and long variable names

SallyS — Thu, 22 Oct 2015 11:07:22 GMT

I added my code and the results from the log in my response to message 3. I have no control over the table itself. If I did I would not have created such long names!!

Re: SAS Entreprise Guide and long variable names

SASKiwi — Thu, 22 Oct 2015 18:44:24 GMT

If you view the table properties in the EG server list for this table what does it report for the long column names?

Re: SAS Entreprise Guide and long variable names

SallyS — Fri, 23 Oct 2015 12:29:55 GMT

The results from the 'table properties' is:

10 AccountingDetail_ExtendedClaimSt Num 8 11. 11. AccountingDetail_ExtendedClaimStatusID
11 AccountingDetail_ExtendedClaimSt Char 255 $255. $255. AccountingDetail_ExtendedClaimStatus
12 AccountingDetail_ExtendedClaimSt Char 50 $50. $50. AccountingDetail_ExtendedClaimStatusMrp

Showing that EG truncates the names but does not do the adjustment (i.e., truncate to 31 and add a number) to make them unique.

I am truly at a loss so appreciate any help.

Thanks,

SallyS

Re: SAS Entreprise Guide and long variable names

SallyS — Fri, 23 Oct 2015 14:38:34 GMT

Thank you all for trying to help me with this.

I have found the solution and am so very happy!

All I had to do was add 1 line (options VALIDVARNAME=V7; run;) prior to my sas program.

Perfect results.

Thanks again.

Sally

Re: SAS Entreprise Guide and long variable names

SASKiwi — Fri, 23 Oct 2015 21:25:28 GMT

Great! That was going to be the next thing I was going to suggest. VALIDVARNAME = ANY is set as default in EG which is not the default if you use SAS outside of EG. For compatibility it is good to set VALIDVARNAME = V7 for all EG use unless you need to maintain non-standard column names for some purpose.