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.
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.
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.
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
It says it is a number, length is 8, format is date9.
And what's your query that's not working, can you post the code?
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.
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.
ah maybe I will try that in my where statement and see if that helps. i will let you know tomm.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.