SELECT CASE WHEN RCM_PAY_STT_DT = DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), MONTH(CURRENT_TIMESTAMP), 1)
THEN (7) - SUM(MAX_MONTHS) OVER (PARTITION BY HICNO) + MAX_MONTHS
ELSE MAX_MONTHS
END AS FORCASTED_MONTHS_MY
FROM MY_TABLE
Thank you,
Shailesh
The only problem statement is:
OVER (PARTITION BY HICNO)
This isn't ANSI SQL, but a database specific. I don't believe there is a direct way to translate this to ANSI SQL.
If you post test data (a few rows) as a datastep:
And what you want the result to look like, its probably simpler to use a datastep. I would say, work out the sum() part in one datastep, then merge that back to the data and do your if (replacement for case when).
Since not everyone is going to immediately recognize the flavor of SQL posted then you shoud provide which instructions need implementation in SAS proc sql and what they do in the source code.
Also it may help to provide a small example data set and the desired result.
Thank you for quick response,
Sorry i can't load data, since its has some PHI, But in my query, I cross join with table 2 where I select MAX(RCM_PAY_STT_DT) and compare it that record is in current month or not, If its in current month I predict as 7 for my calculation, if the record is not in current month, then I put max_months. Like record one has max_month = 3 then I predict FORCASTED_MONTHS_MY = 7, record 2(a) and 2(b) has max_months = 2 and 1 then FORCASTED_MONTHS_MY = 2 and 5 since its total should be 7 (group by recordid).
You understand what I mean.
SELECT
CASE WHEN RCM_PAY_STT_DT = DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), MONTH(CURRENT_TIMESTAMP), 1)
THEN (7) - SUM(MAX_MONTHS) OVER (PARTITION BY HICNO) + MAX_MONTHS
ELSE MAX_MONTHS
END AS FORCASTED_MONTHS_MY
FROM MY_TABLE
CROSS APPLY (
SELECT MAX(MY_Table2.RCM_PAY_STT_DT) AS RCM_PAY_STT_DT
FROM MY_Table2
WHERE MY_Table2.RCM_HICN_ID = MY_TABLE.HICNO
AND MY_Table2.RCM_CMS_CTC_ID = MY_TABLE.PLAN_ID
AND MY_Table2.RCM_PBP_ID = MY_TABLE.PBP
AND MY_Table2.RCM_ST_CTY_CD = MY_TABLE.SCC
AND MY_Table2.RCM_RAF_TP_CD = MY_TABLE.RAFT_PART_C
) B
ORDER BY MY_TABLE.HICNO
@Sp72 wrote:
Thank you for quick response,
Sorry i can't load data, since its has some PHI
Make up data that reflects your situation. Exact data is not required.
Include both what you have and what you need.
TABLE 1 (
HICNO PBP SCC MAX_MONTHS FORCASTED_MONTHS_MY
123 001 A12 3 7
1234 001 A12 1 1
1234 002 A12 2 6
321 003 A13 2 2
321 003 A14 1 5
231 005 A16 1 1
.....
TABLE 2 (updated monthly and Forcasted months determined by monthly)
HICNO RCM_PAY_STT_DT
123 01/01/2017
123 01/02/2017
123 01/03/2017
1234 01/01/2017
1234 01/02/2017
1234 01/03/2017
321 01/01/2017
321 01/02/2017
321 01/03/2017
231 01/01/2017
....
And the final result for that example data would look like ???
Table 1, with derived column "Forecasted_Months_my"
TABLE 1
HICNO PBP SCC MAX_MONTHS FORCASTED_MONTHS_MY
123 001 A12 3 7
1234 001 A12 1 1
1234 002 A12 2 6
321 003 A13 2 2
321 003 A14 1 5
231 005 A16 1 1
.....
123 is exactly the same as 1234 in your sample data yet the output differs for them?
What are we missing?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.