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,
a
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.