i am no expert in regular expressions,  this code can be definitely enhanced or there could be better code. I got the results required for your sample. as @Reeza explained more datapoint, the better the answer can be. Another important please set length of variable as you want(prxchange defaults the length to 200). I have fixed length for one variable  data have; input line $ 1-115; datalines; 4278 01234567 Auto Enroll Provider FAMILY HEACARE 008 02-02 01/01/2018 131.23 0287-HSP OUT SR 1-RT BE 009-COSNF 4273 34876539 Original Provider BURKE CENTER 003 02-02 01/01/2018 187.01 0287-HSP OUT SR 1-RT BE 010-COSNA 1468 04876298 Collapsed Provider PHOENIX RISES OF NJ INC 005 02-02 01/01/2018 17.67 0160-D&TCS 1-RT BE 009-COSNF 1540 10983746 Genesis Provider COLLEGE PARK 016 02-02 01/01/2018 148.43 0160-D&TCS 1-RT BE 013-INVRATECD 1671 34098734 Auto Enroll Provider THIS CNTY DOH 004 02-02 01/01/2018 138.70 0287-HSP OUT SR 1-RT BE 040-INVREQ 1671 45398726 Auto Enroll Provider THAT CLINIC 004 02-02 01/01/2018 138.70 0287-HSP OUT SR 1-RT BE 009-COSNF ; data want; set have; length rate $4; rate =scan(line,1,''); ID= scan(line,2,'') ; provider = prxchange('s/(.+?)([A-Za-z\s]+?Provider)(.+)/$2/', -1, line); *provider = prxchange('s/(.+?)(.+)/$2/', -1, line); name= prxchange('s/(.+?[A-Za-z\s]+?Provider)([A-Z\s]+)(.+)/$2/', -1, line); provid = prxchange('s/(.+?)([0-9]{3})(.+)/$2/', -1, line); type = prxchange('s/(.+?)(\d{2}\-\d{2})(.+)/$2/', -1, line); date = prxchange('s/(.+?)(\d{2}\/\d{2}\/\d{4})(.+)/$2/', -1, line); amount = prxchange('s/(.+?)(\d{2}\/\d{2}\/\d{4}\s+)([0-9\.]+)(.+)/$3/', -1, line); desc=prxchange('s/(.+?)(\d{2}\/\d{2}\/\d{4}\s+)([0-9\.]+\s+)(.+?)(1\-.+)/$4/', -1, line); rt=prxchange('s/(.+?)(\d{2}\/\d{2}\/\d{4}\s+)([0-9\.]+\s+)(.+?)(1\-.+)/$5/', -1, line); run;   ... View more

Not sure, how efficiently this will be as it is doing multiple queries in same query but this will give the solution you want  proc sql; select *, case when year in (select year from have where activity not in (select activity from have where year in (select min(year) from have))) then 2 else 1 end as new_id from have; ... View more

is this dataset in SAS or in any database ... View more

for the first criteria 001 also qualifies right because 07/01/2016 also within 6 months and this where I got confused. I am sorry, if this does not make sense.   001 initial interview 02/01/2016 001 service delivered 07/01/2016 001 re-interview 08/01/2016 001 service delivered 10/01/2016 ... View more

  your data and questions are confusing to me   you said  The only client meeting the criteria is client 002 and part of client 004. Because: Client 001 had a service after the initial interview that was delivered after 6 months of the initial interview. but 002 also has same issue   002 initial interview 02/01/2016 002 service delivered 03/01/2016 002 re-interview 08/01/2016 002 service delivered 10/01/2016 ... View more

you need not change the order of columns. All you need to do is to use option dbcreate_table_opts as shown below   data tdtable.employee_scoring (dbcreate_table_opts= 'primary index(employee_number)'); set work.employee_scoring(keep= Job_type employee_number Sal, Sal_rating); run; ... View more

made up an example, may be something like this.   data have1; input name $ event:mmddyy10.; format event mmddyy10.; datalines; smith 09/23/2005 Sam 10/11/2016 Pam 05/15/2016 ; run; data have2; input name $ event1:mmddyy10. price; format event1 mmddyy10.; datalines; smith 09/23/2005 10 smith 09/02/2005 20 smith 09/24/2005 25 smith 09/28/2005 35 Sam 10/11/2016 22 Sam 10/12/2016 17 Sam 10/11/2016 60 Sam 10/02/2016 70 Pam 05/18/2016 50 ; run; proc sql; select a.name, event1, price from have1 a inner join have2 b on a.name =b.name and (a.event=b.event1 or a.event=b.event1 -1 or a.event=b.event1 +1); ... View more

did you try my answer, I think it works for 2nd part of question too ... View more

something like this  data abc; infile datalines missover dlm ='09'x; input ID 1-2 Store $ 3-4 Month1 $ Month2 $ Month3 $ Month4 $ Month5 $ Month6 $ Month7 $ Month8 $; datalines; 1 A Level1 Level1 Level1 Level1 2 A Level1 Level1 3 B Level1 Level1 Level1 Level1 Level1 4 C Level1 Level1 Level2 Level2 Level2 5 B Level1 6 B Level1 Level1 Level1 Level3 Level3 Level1 Level1 7 A Level1 Level1 8 Level1 Level1 Level1 Level1 Level1 9 D Level1 Level4 Level4 Level4 Level4 Level4 Level4 10 D Level1 Level1 Level1 11 E Level1 12 E Level1 Level1 Level1 Level1 Level1 Level1 Level1 13 D Level1 Level1 14 B Level1 Level1 Level1 Level3 Level3 Level3 Level3 15 A Level1 Level1 Level1 Level1 Level1 16 D Level1 Level1 17 B Level1 Level1 Level1 18 E Level1 Level1 Level1 19 F Level1 Level1 Level1 Level1 Level1 20 D Level1 Level1 Level1 Level1 Level1 21 A Level1 Level1 Level1 Level1 Level1 Level1 Level1 22 A Level1 Level1 Level1 Level1 Level1 Level1 Level1 23 D Level1 Level1 Level1 Level1 Level1 Level1 Level2 24 E Level1 Level1 ; data abss; length newval $ 100; set abc; tot= 0; array varsnew (8) $ newmonth1-newmonth8; array vars (8) $ month1-month8; do i = 1 to 8; if vars{i} = '' then varsnew{i} = '-'; else varsnew{i} =vars{i}; if vars{i} not in ('Level1') then tot=tot+1; else tot=tot+0; end; newval= cats(of newmonth1-newmonth8); if index(newval,'--' )> 0 or tot >1 then delete; drop i newmonth1-newmonth8 newval tot; run; ... View more

 As @Reeza informed like cannot be used in datastep and showed how to get the same  in datastep   below is example for  same.   http://support.sas.com/kb/43/303.html ... View more