Thank you Sooo much!!! this did exactly what I needed! And this worked on the 99.99% of the data. The one that it did not work on (oddly) was on the code that created provid. It was supposed to capture the three digit after the name variable (i.e. 008, 003, 005 etc.) but interestingly it captured and displayed three digits from the rate variable? suggestions on how to fix that? BTW. I checked and double checked and there were no delimiters (space, tab or any other items ) that I could possibly use, but the PRXchange worked!!!! I knew there were people here who were savvy with SAS that even if given a dataset that at this time and age one should not be getting we had people who could figure it out!!! You all are awesome!!! 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
