@drshashlik  SAS will shorten the variable names to 32characters when reading the data from the database into SAS (creating a SAS table). Within SAS code just use the first 32 characters as variable names and you should be fine. The approach when interfacing with a database is to reduce data volumes on the database side as much as you can prior to loading it into SAS.    You can formulate your query run against a database using SAS SQL flavor. SAS will push as much of the processing to the database (like a where condition) but this doesn't always work - i.e. when you use a SAS function in the where condition SAS can't translate into a matching database SQL function.  The other option is to use explicit SQL pass-through. This is SQL formulated in the database flavor which SAS just send directly for execution "as is". This allows you to use any of the database functionality.   I can't really find sufficient information in your question to provide more ideas how to make things work for you. ... View more

With a variable name like qtr_a I might expect the value to be a date, especially since you are matching it on date_cpi, but it might be the qtr_a represents the first day of a quarter, such as 1 Jan 2010 and so there would be no matches for date_cpi if the values represent any day of the year.   A complete guess of a possible solution might be something like ON A.qtr_a = intnx('quarter', B.date_cpi,0,'B') ; which would shift the b.date_cpi value to the first day of the calendar quarter for the comparison. You should verify what the values of A.qtr_a look like before attempting this though.   We know absolutely nothing about what/when/why any of your date_dispensed or date_cpi were collected. You would have to know if it makes sense to compare those.   One might also suspect there maybe should be some other identifier involved in the Join or Where criteria.   ... View more

For more on this topic, including a complete example, see  "Find points where a regression curve has zero slope."   ... View more

I favor the second option with a function and format created from it. The only thing I would do different is using a select() instead of the if/then/else chain: proc fcmp outlib= work.funcs.season; function whatseason (date) $ 15; length return $ 15; /*code*/ select (month(date)); when (1, 2, 12) return="Summer"; when (3, 4, 5) return="Autumn"; when (6, 7, 8) return="Winter"; when (9, 10, 11) return="Spring"; otherwise return="????"; end; return(return); endsub; run; ... View more

Thank you, StatDave, I would dearly love to upgrade to the newest, shiniest, version of SAS, but alas my employer is a government agency with a ZBB for stuff they don't understand.  Thus I suffer. ... View more