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 )
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)
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.
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.
SAS makes certain assumptions about the data it is reading from SQL Server. These are documented here:
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.
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
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.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.