If i understand your question properly this is how i would do it.  Im sure there are more elegant ways to do this. Thanks data have; input id $50.; datalines; CME 1199 DLS 1213 WRF 1155 RTY 1345 ; run; data want;     set have;     do i=1 to countw(id) by 2;         sep_id = catx(' ',scan(id,i), scan(id, i+1));         output;     end;     drop id i; run; ... View more

Once i put the top level query into a sub query it all worked great ... View more

To the best of my knowledge,  The having statement occurs after all calculations and the CALCULATED statement is used inside the select statement if you use a calculated field in another expression to prevent the system from having to perform it more then once. ... View more

Here is the full non working code.  If im understanding you correctly then,  I will have to set both up as a sub query or build separate tables before i do the join? proc sql;     create table tmp_mat_sorted as         select customer, gd, apg1,             case                 when golden_usage_type = 'AA' then 'AA'                 else finance_contract_name             end as finance_contract_name,                     case /*create period data groups*/                 when "&min_date"d < datepart(transaction_date) <= "&max_date"d then 'Add_Period'                 when INTNX('YEAR',"&min_date"d,-3,'S') < datepart(transaction_date) <= INTNX('YEAR', "&min_date"d,-2,'S') then 'Drop_Period'                 else 'Overlap'             end as period,             golden_usage_type as usage_type, part_number,             transaction_date, transaction_quantity, loco_count, yield                 from dfrin.tbl_material_txn_raw u                     Left join (select distinct customer, finance_contract_name,                                         avg(no_of_locomotives) as loco_count, yield,                                             case                                                 when &add_start < fiscal_month <= &add_end then 'Add_Period'                                                 when &drop_start < fiscal_month <= &drop_end then 'Drop_Period'                                                 when &olap_start < fiscal_month <= &olap_end then 'Overlap'                                             end as period                                         from tmp_loco_counts                                             natural left join dfrin.tbl_yield_rates                                         group by customer, finance_contract_name, yield, period                                         having period ne '') c                                 on u.customer=c.customer and u.finance_contract_name=c.finance_contract_name and                                     u.period=c.period                             where INTNX('YEAR',"&min_date"d,-3,'S') < datepart(transaction_date) <= "&max_date"d  and             %_eg_WhereParam(CUSTOMER, Customer, IN, TYPE=S, IS_EXPLICIT=0 ) and             %_eg_WhereParam(GD, GD, IN, TYPE=S, IS_EXPLICIT=0 ) and             %_eg_WhereParam(usage_type,usage_type, IN, TYPE=S, IS_EXPLICIT=0 )         order by customer, gd, apg1, finance_contract_name, usage_type, part_number, period ; quit; ... View more

Thank you so very  much,  After coding it in with a couple more variables that i wanted to bring over works perfectly.  I was so far out in left field I would have been next year before i got there. ... View more

Thank you all, worked perfectly had not heard of that function. ... View more

Thats correct i dont have write access to the /server/sas/lev1/sasapp/ location but i also didn't choose to try and write here.  For some reason sas is putting this in front of the location that i want to write to that is my local client at '\\client\c$\sas outputs' ... View more

This is beautifully simple.  I love efficient code.  Thank you everyone for helping me here.   I did not realize format was an option of the sysfunc command.  I love it. ... View more

Let me clarify what i am trying to do.  I need to get the first day of the the current year into a variable in the date9. format.  The code from Hai_kuo works to get the date value into the variable but not in a date9. format.  I am very new to SAS so if there is a more efficient way to get their I would love to learn it.   There is so much that i don't know about this software still. Needed &date_range_min=01JAN2014 Thank you all for the quick responses ... View more