topic Re: SAS format date in SAS Procedures

SAS format date

tmm — Tue, 29 Jan 2013 20:37:17 GMT

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.

Re: SAS format date

Reeza — Tue, 29 Jan 2013 21:05:32 GMT

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.

Re: SAS format date

tmm — Tue, 29 Jan 2013 21:22:27 GMT

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.

Re: SAS format date

Reeza — Tue, 29 Jan 2013 21:26:19 GMT

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

Re: SAS format date

tmm — Tue, 29 Jan 2013 21:45:11 GMT

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

Re: SAS format date

Reeza — Tue, 29 Jan 2013 21:50:26 GMT

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

Re: SAS format date

tmm — Tue, 29 Jan 2013 22:02:19 GMT

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.

Re: SAS format date

Reeza — Tue, 29 Jan 2013 22:17:47 GMT

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.

Re: SAS format date

tmm — Tue, 29 Jan 2013 23:04:43 GMT

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