Outer Apply available in proc sql?

awmeyertimmy — Thu, 24 Mar 2016 20:45:30 GMT

I have the following code which is based off a SQL query I use that I want to put into SAS via proc sql. Is the outer apply function available in proc sql? If not, how would one modify this code to work in proc sql?

PROC SQL;
Drop Table AWM.temp4;
create table AWM.temp4 as
SELECT DISTINCT
r.*
,c.*
FROM AWM.temp3 r
OUTER APPLY (Select top 1 c.ScoreDt,
Case WHEN c.ScoreModelDimId = 35 THEN ISNULL(c.CreditScore,0) END AS TS,
Case WHEN c.ScoreModelDimId = 24 THEN ISNULL(c.CreditScore,0) END AS EX2,
Case WHEN c.ScoreModelDimId = 32 THEN ISNULL(c.CreditScore,0) END AS HF18,
Case WHEN c.ScoreModelDimId = 12 THEN ISNULL(c.CreditScore,0) END AS XPN1,
Case WHEN c.ScoreModelDimId = 20 THEN ISNULL(c.CreditScore,0) END AS JM1,
Case WHEN c.ScoreModelDimId = 34 THEN ISNULL(c.CreditScore,0) END AS DMS2
FROM Portrevw.VwCreditScoreFact c
where r.AccountDimId = c.AccountDimId
AND c.ScoreModelDimId IN (35,24,32,12,20,34)
AND c.ValidCalc = 1
AND c.ScoreDt > '01DEC2014'd
Order by c.ScoreDt desc) as c;
QUIT;

Thanks,

Re: Outer Apply available in proc sql?

ChrisNZ — Tue, 29 Mar 2016 03:04:33 GMT

APPLY is a SQL Server language extension. It is not available in SAS.

Whether SAS can give out the same result will depend on your data, a left join can usually replace OUTER APPLY.

See https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/ for an explanation.

topic Outer Apply available in proc sql? in SAS Programming

Outer Apply available in proc sql?

Re: Outer Apply available in proc sql?