topic Re: How you can convert this SQL to PROC SQL? in SAS Enterprise Guide

How you can convert this SQL to PROC SQL?

Sp72 — Mon, 06 Mar 2017 15:11:34 GMT

FROM MY_TABLE

Thank you,

Shailesh

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

RW9 — Mon, 06 Mar 2017 15:25:34 GMT

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-data-AKA-generate/ta-p/258712

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

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

ballardw — Mon, 06 Mar 2017 15:27:30 GMT

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.

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

Sp72 — Mon, 06 Mar 2017 16:41:50 GMT

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.

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

Sp72 — Mon, 06 Mar 2017 16:43:07 GMT

You understand what I mean.

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

Sp72 — Mon, 06 Mar 2017 16:48:46 GMT

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

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

Reeza — Mon, 06 Mar 2017 19:17:27 GMT

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

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

Sp72 — Mon, 06 Mar 2017 20:39:53 GMT

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

....

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

ballardw — Mon, 06 Mar 2017 21:06:29 GMT

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

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

Sp72 — Mon, 06 Mar 2017 21:20:30 GMT

Table 1, with derived column "Forecasted_Months_my"

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

Sp72 — Mon, 06 Mar 2017 21:24:59 GMT

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

.....

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

Reeza — Mon, 06 Mar 2017 21:27:54 GMT

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

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

Sp72 — Tue, 07 Mar 2017 12:10:26 GMT

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.