may be something like this. I have not tested this   proc sql; create table final as select * from  (select * from a left join status_table b  on id=id inner join balance_table c on id=id)a where not(product in ('MC1', 'MC2') and Status='CLOSED' and balance='' ); quit; ... View more

Can you show where the join fails. If your query fails it might be on dates. Can you add this and try, whether it will help with your query times. I have seen moving data from sql server to SAS for nearly 3 million records takes around 30 minutes with 100 columns, so you can estimate an approximate time.   options dbidirectexec sastraceloc=saslog;   ... View more

may be something like this proc sql; create table file01 as select date, var1, var2 from table1 union all select date, var1, var2 from table2 where date le '19NOV2013'd; create table file02 as select date, var1, var2 from table3 union all select date, var1, var2 from table2 where date gt '19NOV2013'd; quit; ... View more

It is much better off doing this in datastep and code by @PaigeMiller is much cleaner and I think will be efficient too. But you may able to get the answer by sql with below code. Sorry I just saw that you are running this query in an explicit pass through and sql query I gave will not in explicit pass through as it has sas compoenents in it. Below query should work in explicit pass through.   data abc; input transaction_id transaction_date:mmddyy10.; format transaction_date date9.; datalines; 124 6/14/2017 656 6/14/2017 124 4/17/2017 656 7/23/2016 656 4/17/2016 124 3/15/2016 124 12/20/2014 656 4/16/2014 333 8/5/2013 454 11/5/2012 656 1/20/2011 454 10/22/2010 233 4/30/2010 ; proc sql; select distinct bde.transaction_id, first_transaction_date , second_transaction_date , last_transaction_date from (select a.transaction_id, first_transaction_date, last_transaction_date from (select transaction_id, transaction_date as first_transaction_date from abc group by transaction_id having transaction_date = min(transaction_date))a inner join (select transaction_id, transaction_date as last_transaction_date from abc group by transaction_id having transaction_date = max(transaction_date))b on a.transaction_id =b.transaction_id)bde left join (select a.transaction_id, min(a.transaction_date) as second_transaction_date from (select transaction_id, transaction_date from abc)a inner join (select transaction_id, min(transaction_date)as min_trasaction_date from abc b group by transaction_id)b on a.transaction_id = b.transaction_id and a.transaction_date > min_trasaction_date)cvcv on bde.transaction_id =cvcv.transaction_id;     ... View more

please show sample input data and output data you want to have . Many of us will not open attachments for various issues like virus etc. ... View more

may be something like this. proc sql; select a.*, case when a.id =b.id and b.cnt ge 4 then 'serious' else 'ok' end as condition from (select * from abc)a left join (select distinct id, year(date) as year, count(*) as cnt from abc group by id, year(date))b on a.id =b.id; ... View more

may be something like this proc sql; select * from yourtable group by prxchange('s/(\d+|#).*$//i',-1, pharmacy) having count(prxchange('s/(\d+|#).*$//i',-1, pharmacy))>10;  to check what multiple store have, along with its parent name proc sql; select *, prxchange('s/(\d+|#).*$//i',-1, pharmacy) as parentcompanyname from have group by parentcompanyname having count(parentcompanyname)>10;   or to just companies  with parent name   proc sql; select prxchange('s/(\d+|#).*$//i',-1, pharmacy) as parentcompanyname from ac group by parentcompanyname having count(parentcompanyname)>10; ... View more

Please provide a small sample of data you have and output you want. So that someone will able to easily intrepret, what you are trying to say ... View more

aha, i missed that part ... View more

then something like this proc sql; select a.var1 as original, b.var_new as truncatedvalue from have a inner join have b on b.var_new = substr(a.var1,1,50) and a.var1 ne b.var_new; quit; ... View more