Desktop productivity for business analysts and programmers

How you can convert this SQL to PROC SQL?

Reply
Occasional Contributor
Posts: 8

How you can convert this SQL to PROC SQL?

 

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

Esteemed Advisor
Esteemed Advisor
Posts: 7,190

Re: How you can convert this SQL to PROC SQL?

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

Occasional Contributor
Posts: 8

Re: How you can convert this SQL to PROC SQL?

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.
Grand Advisor
Posts: 10,196

Re: How you can convert this SQL to PROC SQL?

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.

Occasional Contributor
Posts: 8

Re: How you can convert this SQL to PROC SQL?

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.

Occasional Contributor
Posts: 8

Re: How you can convert this SQL to PROC SQL?

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

Grand Advisor
Posts: 17,310

Re: How you can convert this SQL to PROC SQL?


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. 

Occasional Contributor
Posts: 8

Re: How you can convert this SQL to PROC SQL?

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

....

 

 

 

 

Grand Advisor
Posts: 10,196

Re: How you can convert this SQL to PROC SQL?

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

Occasional Contributor
Posts: 8

Re: How you can convert this SQL to PROC SQL?

Table 1, with derived column "Forecasted_Months_my" 

Occasional Contributor
Posts: 8

Re: How you can convert this SQL to PROC SQL?

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 

.....

Grand Advisor
Posts: 17,310

Re: How you can convert this SQL to PROC SQL?

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

Occasional Contributor
Posts: 8

Re: How you can convert this SQL to PROC SQL?

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.
Ask a Question
Discussion stats
  • 12 replies
  • 177 views
  • 0 likes
  • 4 in conversation