SAS format date

[tmm]

I am extracting from a table that has our dates formatted as:

Date/Time

When an extraction is done from the database, the date comes over like this:

taxid          market                       market_eff_dt        market_end_dt

1111       new england                  01jan1997              31dec9999

1111       dallas                             01jan1996              31dec9999

I have 30k records and it gives me tons of dupes and what I want when I query is the max of the market_eff_dt yet when I code that it does not work. Even putting in max(market_eff_dt) as market_eff_dt format=date9.,

It will format the date fine but still returns the 2 rows and I know it has something to do with some date thing with SAS where it see's 01jan and says ok that is the max. Just like if I had 01Sep1996 and 01Oct1995, it would return 01Oct because that is the max but it is not because 1996 is later than 1995. I am just not sure how to code it right.

[Reeza]

If it's datetime then you need the format dtdate9., but it should be pulling the appropriate maximum.

You should show the code if its still not working.

That date thing you're talking about is have a date as a character rather than a number. Characters '01' are ordered the same and then you order the next characters, O and S. So Oct will come before September, because O comes before S in the alphabet. 

[tmm]

Right, and that is the problem I am running into. It is not the datetime then and doing a max. It is something where I cannot do a max on the date as it shows 01jan1996, 01jan1997. Because 01jan is the max I need to figure something else out so that it looks at 1997 in this case. is there a way to look at the max of a date and just look at the year? that might work.

[Reeza]

Run a proc contents on your data and post the results of that variable. Is it a character or number? What format is applied? What length?

My suggestion would be to convert it to a date in your query using something like:

max( input(date, mmddyy10.) ) as max_date

[tmm]

It says it is a number, length is 8, format is date9.

[Reeza]

And what's your query that's not working, can you post the code?

[tmm]

sure. I have to type it because it is on my work computer and since I work in healthcare have to chng things for HIPAA. hopefully there will be no typos. I don't get an error. the code runs fine, just not taking the max of the date:

(select distinct

mbr_sys_id,

substr(clm_aud_nbr,1,10) as claim,

fst_srvc_dt,

max(mkt_row_eff_dt) as mkt_row_eff_dt format=date9.,

mkt_row_end_dt,

sys_drg_cd

from pci.all

where mkt_row_end_dt >='12/31/9999'

group by

mbr_sys_id,

clm_aud_nbr,

fst_srvc_dt,

mkt_row_end_dt,

prov_tin,

sys_drg_cd);

quit;

It will not remove the earliest mkt_row_eff_dt. all items are identical except the mkt_row_eff_dt and market name piece

so like example return is:

mbr_sys_id  claim    fst_srvc_dt   mkt_row_eff_dt  mkt_row_end_dt   prov_tin  sys_drg_cd marketname

111                111     01jan2011     01jan1996        12319999             123         270               maine

111                111     01jan2011     01jan1997        12319999             123         270              new england

I want the maximum of the mkt row eff dt because I know for a fact this provider tin moved from practicing at the hospital in maine to the hospital in new england therefore I need to say i want the maximum of that effective date so the market names are correct match ups to the provider TINS. I wished we would just put an end date on the mkt_row_end_dt when providers move practices so 01jan1996 the mkt_row_end_dt would be 01jan1997 cuz they started practing at a new place. would make life so much easier but that is not how our claims system is set up and i would love to tell them to chng it but we are the largest insurer in the world and have 60 million members we cover with 104k employees and they are not going to listen to a measly programmer.

[Reeza]

Is this '12319999' valid date in SAS or is that field character?

Anyways, select max should work from what I see.  But it would be by all your group variables. I'm not 100% sure I understand what you want

Are you looking to add

having max(mkt_row_eff_dt)=mkt_row_eff_dt to your where clause.

[tmm]

ah maybe I will try that in my where statement and see if that helps. i will let you know tomm.