proc sql pass through facility concatenation of variable

[CP2]

Calling all SQL and SAS Pass-Through Facility Experts !!

Hello, I am having trouble with Proc SQL pass through facility when creating a concatenated variable. The SQL was acquired from a seasoned SQL programmer and uses CTE (With) and Cross Apply statements and I placed it into a PROC SQL with EXECUTE statements.

My variable,typenames, defaults to 1024 character length even when using the LEFT() function that typically our server recognizes when running the query in SQL (I changed the snippet below to this and it still saved the field as 1024 length: LEFT(REPLACE(LEFT(type, LEN(type) - 1), '; unknown', ''),45) AS typenames. I am getting the right results from the code below in terms of content but the field length format ends up being 1024 which is the default cutoff for long strings when it really should only be 45 which is the max length of the field (makes for a lot of blank spaces).

The total code is too long but here is a pared down snippet I am referencing.  Basically I want all rows with a valid type, excluding unknowns unless unknown is the only response; however, the actual coding to get this is not in question (I'm sorry I can't provide more explanation but since it is acquired code I am not sure what the data looks like in the various SQL database tables or how to decipher the cross apply) . My problem is why my variable typenames gets saved as a 1024 character field. If someone has come across this and knows a fix I can add to the proc sql that would be ideal. Otherwise I just have to add a datastep to change formats of all the concatenated fields coded like this.

snippet section of SQL code:

WITH    type

          AS ( SELECT DISTINCT

                        a.ID ,

                        Name

               FROM     #temp1 a

                        INNER JOIN schema.file1 pm ON d.ID = pm.id

             )

    SELECT  DISTINCT

            ID ,

            REPLACE(LEFT(type, LEN(type) - 1), '; unknown', '') AS typenames

    INTO    #Concatenation

    FROM    type pth

            CROSS APPLY ( SELECT    Name + '; '

                          FROM      type pth2

                          WHERE     pth.ID = pth2.ID

                        FOR

                          XML PATH('')

                        ) AS x ( type )

[LinusH]

So you are actually asking for target DBMS SQL syntax? Then you are better off asking specific forum for your database.
As a SAS forum we could help on the part when the data is comming into SAS.

[CP2]

The SQL syntax works in ms sql studio and in the SAS Proc SQL pass-through except for the fact that SAS does not recognize the length of the concatenated field so it gives it the default length of 1024. It appears to be more of a SAS issue than SQL syntax. I was hoping there would be a tip or trick to use in the SQL code so that proc sql will recognize the max length without having to perform a separate datastep to change my concatenated variables. I already got tips from SQL developers for various ways to pull the first 45 characters but no luck. I think SAS proc SQL doesn't know how to handle the cross apply concatenation...in terms of how to deal with length because the logic of it is working (that is, I am getting the right content in the field)

[Shmuel]

Assume your logic and code are right, in oreder to overcome the length of 1024

replace the LEFT function into STRIP function and post result if you are not satified with.

[CP2]

Thank you for the suggestion. The strip function is not recognized by our SQL server so I get an error. Because I'm using pass-through facility  I have to use functions recognized by the SQL database. 

[SASKiwi]

SAS makes certain assumptions about the data it is reading from SQL Server. These are documented here:

http://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#n0h4i25zq3t58en1lg...

There are a couple of ways you can handle character columns that are much longer than required.

First is to simply let the SAS default length happen, then apply COMPRESS = YES or COMPRESS = BINARY to the SAS table. This will ensure that the impact of the onger character columns is minimal in terms of disk storage. I like this approach because it is simple and easy to apply.

Second is to explicitly set the length of the column as it is being created:

proc sql;
   connect to SQLSVR as mydb
      (datasrc="SQL Server" user=myusr1 password=mypwd1);
   create table Customers as
   select  customer length = 8
          ,name     length = 30
          ,country  length = 20
    from connection to mydb
      (select CUSTOMER, NAME, COUNTRY
          from CUSTOMERS
          where COUNTRY <> 'USA');
quit;

The disadvantage of this approach is having to explicitly list all of the columns and their lengths. It does avoid the need to compress your output SAS table though.

[CP2]

Thank you for your suggestions. Neither of these options worked to reduce the length of the field. The compression did reduce the overall file size by 94%! I will add a datastep to reduce the length and not bother to do it within the proc sql.

[Patrick]

@CP2

In my understanding SAS will determine the length and type of variables based on what's coming from the source database.

Do you know the type and length your database assigns when creating typenames?

REPLACE(LEFT(type, LEN(type) - 1), '; unknown', '') AS typenames

Eventually amend your pass-through code and explicitely control what you get for typenames.

From the docu:

If string_expression is not of type varchar(max) or nvarchar(max), REPLACE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to a large-value data type.

https://technet.microsoft.com/en-us/library/ms186862(v=sql.110).aspx

[Randy2]

Not sure if this helps but..

to get the length of all of the columns I use the datasaets procedure and load the metadata, ie length of columns into a macro array.  Then I use the macro array to build the SQL.

[Randy2]

Should have also said I think some of the databases support the substr function. Might be called something different like substring. Maybe this will help.