DATA Step, Macro, Functions and more

Convert the code

Accepted Solution Solved
Reply
Contributor
Posts: 56
Accepted Solution

Convert the code

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


Accepted Solutions
Solution
‎12-23-2017 01:07 AM
Esteemed Advisor
Posts: 5,392

Re: Convert the code

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.

 

PG

View solution in original post


All Replies
Super User
Posts: 3,764

Re: Convert the code

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

Contributor
Posts: 56

Re: Convert the code

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

Super User
Posts: 3,764

Re: Convert the code

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.

Esteemed Advisor
Posts: 5,392

Re: Convert the code

In SAS,

 

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

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

 

PG
Contributor
Posts: 56

Re: Convert the code

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.

Super User
Posts: 22,823

Re: Convert the code


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

Contributor
Posts: 56

Re: Convert the code

HI Reeza,

I would like to replicate the logic and calculate the age.
Super User
Posts: 22,823

Re: Convert the code

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 

 

 

Contributor
Posts: 56

Re: Convert the code

reeza,

you have a email address I can send you the screen shots of the data
Contributor
Posts: 56

Re: Convert the code

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  

Contributor
Posts: 56

Re: Convert the code

I would like to calculate exact age as of July 1
Solution
‎12-23-2017 01:07 AM
Esteemed Advisor
Posts: 5,392

Re: Convert the code

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.

 

PG
Contributor
Posts: 56

Re: Convert the code

Thank you PG I appreciate your help
Esteemed Advisor
Posts: 5,392

Re: Convert the code

Now you can validate the original Oracle code Smiley Wink!

PG
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 20 replies
  • 488 views
  • 0 likes
  • 4 in conversation