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
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.
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.
Are you going to be querying SAS data or a database table and if so what database?
I will be querying database table and its an oracle database.
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.
In SAS,
age = intck("YEAR", <birthdate>, <date>, "CONTINUOUS");
where <birthdate> and <date> are SAS dates.
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.
@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
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
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
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.