How you can convert this SQL to PROC SQL?

[Sp72]

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

[RW9]

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:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

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

[Sp72]

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.

[ballardw]

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.

[Sp72]

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.

[Sp72]

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

[Reeza]

---

@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. 

[Sp72]

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

....

[ballardw]

And the final result for that example data would look like ???

[Sp72]

Table 1, with derived column "Forecasted_Months_my" 

[Sp72]

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 

.....

[Reeza]

123 is exactly the same as 1234 in your sample data yet the output differs for them? 
What are we missing?

[Sp72]

PBPis different for each ids, they move between PBPs and SCCs.123 stayed all 3 months with same PBP/SCC while 1234 move between PBP/SCC.