I am trying to separate Lab Information that is in one variable into separate variables and separate rows for each instance. Below is an example of what the lab information looks like for one row. This example has 3 labs lumped together, but I have examples with up to 9 labs lumped together. I would like to have the <b>, </b> etc. removed, and the individual column names renamed so they don’t have any spaces. *Also, this example is test data. Example of Variable is in the attachment. Desired Result for each row (some fields are NULL and others have results) LocalID: DateRcdByPH: SpecCollDt: ELR_IND: ResultedTest: CodedResult: NumericResult: TextResults: Comments: Row 1 OBS456571123N01 1/15/2020 1/6/2020 Y RPR Qualitative Stat Reactive * Row 2 OBS45657123IN01 1/15/2020 1/7/2020 Y RPR Quantitative Stat 0.0861111 Row 3 OB456874123N01 3/13/2020 1/5/2020 Y FTA Reactive * I've tried playing around with PRXmatch, but I'm not very familiar with this function to get the desired result I need. I’m also unsure how to rename multiple values within the same variable, so the new column names don’t have any spaces (i.e., changing ‘Local ID’ to ‘LocalID’. Any help is greatly appreciated! Below is the code I currently have. data lab2; set lab; /* Extract the required information from the LABORATORY_INFORMATION column */ LocalID=prxmatch("/(?<=LocalID:)(.*?)(?=\r\n)/", LABORATORY_INFORMATION); DateRcvdByPH = prxmatch("/(?<=DateRcvdByPH:)(.*?)(?=\r\n)/", LABORATORY_INFORMATION); SpecCollDt = prxmatch("/(?<=SpecCollDt:)(.*?)(?=\r\n)/", LABORATORY_INFORMATION); ELR_IND = prxmatch("/(?<=ELR_IND:)(.*?)(?=\r\n)/", LABORATORY_INFORMATION); ResultedTest = prxmatch("/(?<=ResultedTest:)(.*?)(?=\r\n)/", LABORATORY_INFORMATION); CodedResult = prxmatch("/(?<=CodedResult:)(.*?)(?=\r\n)/", LABORATORY_INFORMATION); NumericResult = prxmatch("/(?<=NumericResult:)(.*?)(?=\r\n)/", LABORATORY_INFORMATION); TextResult = prxmatch("/(?<=TextResult:)(.*?)(?=\r\n)/", LABORATORY_INFORMATION); Comments = prxmatch("/(?<=Comments:)(.*?)$/", LABORATORY_INFORMATION); /* Trim and squeeze the extracted values */ LocalID = trim(compbl(LocalID)); DateRcvdByPH = trim(compbl(DateRcvdByPH)); SpecCollDt = trim(compbl(SpecCollDt)); ELR_IND = trim(compbl(ELR_IND)); ResultedTest = trim(compbl(ResultedTest)); CodedResult = trim(compbl(CodedResult)); NumericResult = trim(compbl(NumericResult)); TextResult = trim(compbl(TextResult)); Comments = trim(compbl(Comments)); /* Drop the original LABORATORY_INFORMATION column */ drop LABORATORY_INFORMATION; run;
... View more