Convert the code

[Ahsan]

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

[Ahsan]

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.

[SASKiwi]

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

[Ahsan]

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

[SASKiwi]

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.

[PGStats]

In SAS,

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

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

[Ahsan]

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.

[Reeza]

---

@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

[Ahsan]

HI Reeza,

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

[Reeza]

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 

[Ahsan]

reeza,

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

[Ahsan]

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  

[Ahsan]

I would like to calculate exact age as of July 1

[PGStats · Accepted solution]

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.

[Ahsan]

Thank you PG I appreciate your help