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 )
... View more
