You are not alone. We all have been through it.   You can always verify the current system options by   proc options option=obs define; run;   Or just without define to not flood the log:   proc options option=obs ; run; ... View more

Yes, I twisted the query above and it worked for me. Thank you ... View more

Interesting, I've just noticed that @DBailey had posted another reply. A quick comparison of the results (for made-up input data) has revealed that they differ! I leave it to you, @aperansi, to decide which solution is correct for your purposes. (I'd use your example, the invoice for August 2018, to find out.) ... View more

I like to get more for less Coalesce these date variables to select where only one is non-missing or "product" them to select where all are non-missing PROC SQL ; create table final as select coalescec( a.id, b.id, c.id ) as abc_id , coalescec( a.place, b.place, c.place) as abc_place , coalesce( a.date, b.date, c.date) as abc_date , max( a.date, b.date, c.date) as latest_date , min( a.date, b.date, c.date) as earliest_date , * From test1 a full join test2 b on a.ID = b.ID and a.Place = b.Place full join test3 c on a.ID = c.ID and a.Place = c.Place where a.date * b.date *c.date is not null (Alternatively Where latest_date is not null) order by abc_ID, abc_place; quit; Sorry this is untested as I haven't yet found a way of testing sas code on this smartphone ... View more

thank you ! it works! ... View more

So INTNX('month',......) is used to move dates by a certain # of months.  It also can adjust to the beginning, ending, or the sameday in that new month.    intnx('month',api_date,case when day(api_date)>=25 then 1 else 0 end,'begin') format mmddyy10. as Period, I understood that if the api_date was between the 25th of the previous month and the 24th of the current month, that data was to be tagged as the first of the current month.  That can be translated to "if the api_date is 25 or greater, move to the next month otherwise keep the current month" which is used in the intnx function as the # of months to move and use the beginning of that resulting month.   intnx('month',api_date,case when day(api_date)>=25 then 0 else - 1 end,'begin')+24 format mmddyy10. as PeriodStart, If the api_date >= 25, move to the beginning of the current month and add 24 days (ends up on the 25th).  If the api_date < 25, move to the beginning of the prior month and add 24 days (resulting in the 25th of the prior month). intnx('month',api_date,case when day(api_date)>=25 then 1 else 0 end,'begin')+23 format mmddyy10. as PeriodEnd  if the api_date >= 25, move to the beginning of the next month and add 23 days (ends up on 24th of next month).  If the api_date < 25, move to the beginning of current month and add 23 days (ends up on 24th of current month). ... View more

It worked! Thank you! I also noticed that I had missed to have if y_&i.=&j. then cat&j._time_&i.=1; else cat&j._time_&i.=0; instead of if y_&I.=1 then cat&j._time_&i.=1; else cat&j._time_&i.=0; But that was easily adjusted. Now everything works perfectly!   ... View more

That worked like a charm. Thank you! ... View more