topic Re: Handling / Differentiating identically named variables in source table in SAS Programming

Handling / Differentiating identically named variables in source table

Seb_A_Sanders — Mon, 25 Mar 2019 11:35:00 GMT

The database setup at my organisation is SQL Server tables copied onto our SAS server. The SQL tables were setup to run pre-programmed SQL queries, but now SAS is the tool used. This however creates an issue with some tables having variables that are too long for SAS, but work in SQL. The label for the source variable is correct and not shortened.

The source table (in SQL Server) names:

Consolidated_Arrears_Vs_Portfolio_Balance_Ltd

Consolidated_Arrears_Vs_Portfolio_Balance_Pure

In SAS:

Consolidated_Arrears_Vs_Portfoli

SAS Labels:

Consolidated_Arrears_Vs_Portfolio_Balance_Ltd

Consolidated_Arrears_Vs_Portfolio_Balance_Pure

So, how do I tell the difference between these two when I'm writing code / query builder?

Thanks in advance.

(I have no control over the SQL Server code so I can't change the underlying tables or how these are copied to the SAS server)

Re: Handling / Differentiating identically named variables in source table

RW9 — Mon, 25 Mar 2019 11:49:46 GMT

Use named literals:

select 'consolidated_arrears_vs_portfolio_balance_ltd'n

Note the quotes and the n. Or you can set:

options validvarname=any;

Which basically means you can write any old rubbish you like, and the next guy will love you for it.

Re: Handling / Differentiating identically named variables in source table

Seb_A_Sanders — Mon, 25 Mar 2019 12:12:27 GMT

Hi,

I've tried this, but I'm continuing to receive this message:

PROC SQL;

create table testing
as select
'consolidated_arrears_vs_portfolio_balance_chl'n
____________________________________________
65
ERROR 65-58: Name ''consolidated_arrears_vs_portfolio_balance_chl'N' is too long for a SAS name in this context.

FROM REP.va_me_local;
run;

I have also included the

options validvarname=any;

prior to this.

Thanks,

Re: Handling / Differentiating identically named variables in source table

mkeintz — Mon, 25 Mar 2019 12:24:52 GMT

The validvarname=any option permits special characters and embedded blanks (and leading blanks) in name literals. But unfortunately it does not escape the 32-byte limit. You'll need another approach.

Re: Handling / Differentiating identically named variables in source table

Astounding — Mon, 25 Mar 2019 12:29:57 GMT

I don't know if this will work, but it's worth testing. Have you tried shortening the name on the fly, something like:

select Consolidated_Arrears_Vs_Portfolio_Balance_Ltd as Cons_Arrears_vs_Port_Bal_Ltd

Re: Handling / Differentiating identically named variables in source table

RW9 — Mon, 25 Mar 2019 12:32:37 GMT

Ah, ok then. Looks like your oing to have to go down one of these routes:
https://communities.sas.com/t5/SAS-Data-Management/Long-SQL-Server-via-ODBC-table-names-amp-column-names/td-p/356253

Re: Handling / Differentiating identically named variables in source table

ErikLund_Jensen — Mon, 25 Mar 2019 12:44:01 GMT

Hi @Seb_A_Sanders

I think your only way out of the problem is to use SQL passthru. This way you can handle +32 long names of both tables and columns. Se the following code, which is a subset of working production code cut down to 3 variables:

options Validvarname = V7;
proc sql;

    connect using xkmdne as sconn;

    create table &_OUTPUT (label="archive_NEXUS2_medicine_medication_additional_information") as
        select 
			medication_id 
				length=8 ,
             administratively_deleted_datetim
                length=8
                format=nldatm20.
                label="administratively_deleted_datetime" ,
             last_past_fmk_dosage_period_end_
                length=8
                format=ddmmyyd10.
                label="last_past_fmk_dosage_period_end_date" 

        from connection to sconn (
            select
                medication_id,
                administratively_deleted_datetime     
					as administratively_deleted_datetim,
                last_past_fmk_dosage_period_end_date  
					as last_past_fmk_dosage_period_end_
            from archive_NEXUS2_medicine_medication_additional_information
        );

    disconnect from sconn;
quit;

Explanation:

Make a connection to the relevant SQL database using a previously assigned libname to the database.

Create a new SAS table with name abbreviated to 32 chars. Use original SQL table name as dataset label. select from connection to database, use original SQL column names as variable labels.

Connection to database gives the result of the second select, where long column names are abbreviated to sonething useful. This select is sent to execution in the database, so all long names are kept on the SQL Server side, and SAS sees only your abbreviations.