topic Re: Convert the code in SAS Programming

Convert the code

Ahsan — Sat, 23 Dec 2017 01:36:14 GMT

Hello,

My dba send me this SQL Query which I need to tested in SAS to validate the data

FLOOR (MONTHS_BETWEEN ( TO_DATE((case when to_char(dt.month_bgn_dt, 'MM') > '06' then TO_CHAR(to_char(dt.month_bgn_dt,'YYYY'))||'0701' else TO_NUMBER(to_char(dt.month_bgn_dt,'YYYY')-1)||'0701' end),'YYYYMMDD'),mbr.BRTH_DT )/12) mbr_age_bos

how do I convert this into SAS Query to calculate the age

Help Please

Ahsan — Sat, 23 Dec 2017 01:33:33 GMT

How do calculate the age

Member Age at beginning of the State Fiscal Year July 1. Determine beginning of SFY (yyyy) from RPT_PERIOD (yyyymm). Calculate MBR_AGE_BOS at 7/1/yyyy according to MBR_BRTH_DT.

Please help me with this.

Re: Convert the code

SASKiwi — Sat, 23 Dec 2017 01:49:20 GMT

Are you going to be querying SAS data or a database table and if so what database?

Re: Convert the code

Ahsan — Sat, 23 Dec 2017 01:56:16 GMT

I will be querying database table and its an oracle database.

Re: Convert the code

SASKiwi — Sat, 23 Dec 2017 03:27:38 GMT

OK, then why not just use an SQL passthru query and then you can use the Oracle query as it is? If you are not familiar with passthru here is an example here: http://support.sas.com/kb/19/205.html. Get your query working in Oracle first and then paste it into your PROC SQL step.

Re: Convert the code

PGStats — Sat, 23 Dec 2017 04:43:56 GMT

In SAS,

age = intck("YEAR", <birthdate>, <date>, "CONTINUOUS");

where <birthdate> and <date> are SAS dates.

Re: Convert the code

Ahsan — Sat, 23 Dec 2017 05:11:31 GMT

Hi PG,

here is the business logic

Member Age at beginning of the State Fiscal Year July 1. Determine beginning of SFY (yyyy) from RPT_PERIOD (yyyymm). Calculate MBR_AGE_BOS at 7/1/yyyy according to MBR_BRTH_DT.

and that's the Dba code

I see the data in SAS which dba created and I need to validated.

Re: Convert the code

Reeza — Sat, 23 Dec 2017 05:29:23 GMT

@Ahsan wrote:

Hi PG,

here is the business logic

Member Age at beginning of the State Fiscal Year July 1. Determine beginning of SFY (yyyy) from RPT_PERIOD (yyyymm). Calculate MBR_AGE_BOS at 7/1/yyyy according to MBR_BRTH_DT.

and that's the Dba code

FLOOR (MONTHS_BETWEEN ( TO_DATE((case when to_char(dt.month_bgn_dt, 'MM') > '06' then TO_CHAR(to_char(dt.month_bgn_dt,'YYYY'))||'0701' else TO_NUMBER(to_char(dt.month_bgn_dt,'YYYY')-1)||'0701' end),'YYYYMMDD'),mbr.BRTH_DT )/12) mbr_age_bos

I see the data in SAS which dba created and I need to validated.

Are you looking to calculate the exact age as of July 1 or to replicate the logic in the code above? That's not necessarily the same thing.

Here's a write up on calculating ages in SAS

https://kb.iu.edu/d/aczw

Re: Convert the code

Ahsan — Sat, 23 Dec 2017 05:34:45 GMT

HI Reeza,

I would like to replicate the logic and calculate the age.

Re: Convert the code

Ahsan — Sat, 23 Dec 2017 05:37:08 GMT

I would like to calculate exact age as of July 1

Re: Convert the code

Reeza — Sat, 23 Dec 2017 05:40:19 GMT

It'll help if you post some sample data and expected output.

I'm less inclined to do line to line/function conversion this time of night....but in general,

TO_CHAR -> PUT()

|| -> CATT()

YEAR() will take the year of a date

DATEPART() will extract the date, if it's a datetime

MDY() to calculate date

INTCK() to calculate the number of months between the intervals, also has an AGE parameter

Assuming you have two dates, something like the following:

intck('month', date_birth, mdy(7, 1, year(month_bgn_dt))) / 12

Re: Convert the code

Ahsan — Sat, 23 Dec 2017 05:50:18 GMT

reeza,

you have a email address I can send you the screen shots of the data

Re: Convert the code

Ahsan — Sat, 23 Dec 2017 05:53:13 GMT

I post the screen shot I used your formula and I got the results but you will see test which I am testing. My test age should match MBR_AGE_BOS

Re: Convert the code

PGStats — Sat, 23 Dec 2017 05:55:39 GMT

OK, then the code should be like:

age = intck("YEAR", <mbr_brthdt>, intnx("YEAR.7", <rpt_period>, 0), "CONTINUOUS");

with, again, <mbr_brthdt> and <rpt_period> being SAS dates.

Re: Convert the code

Ahsan — Sat, 23 Dec 2017 06:15:38 GMT

Thank you PG I appreciate your help

Re: Convert the code

PGStats — Sat, 23 Dec 2017 06:17:40 GMT

Now you can validate the original Oracle code !

Re: Convert the code

Ahsan — Sat, 23 Dec 2017 06:32:31 GMT

hi pg,
Yes I validate it looks perfect thank you very much. I just have a quick question I have the same scenario this time the business logic is like this

"Member Age at end of the State Fiscal Year
June 30. Determine end of SFY (yyyy) from RPT_PERIOD (yyyymm). Calculate MBR_AGE_EOS at 6/30/yyyy according to MBR_BRTH_DT."

Can I use the same code and change 7 into 6

Re: Convert the code

PGStats — Sat, 23 Dec 2017 06:35:31 GMT

Not really, you should use

age = intck("YEAR", <mbr_brthdt>, intnx("YEAR.7", <rpt_period>, 0, "END"), "CONTINUOUS");

Re: Convert the code

Ahsan — Sat, 23 Dec 2017 06:40:49 GMT

works like a charm you are the best

Re: Convert the code

Ahsan — Tue, 26 Dec 2017 18:12:24 GMT

Hi PG Stats,

I need your help that 's the Business logic I need to calculate the "Member age at beginning of the month each month. Calculate MBR_AGE_BOM using RPT_PERIOD_BEG_DT.

I appreciate all the help.