I have a ‘procedure code’ column (3 million records). I need to clean the data by categorizing the ‘procedure codes’ column to three different groups as below and then delete the records otherwise.
I hope my code shown below had resolved for the Category 1. But not for category II and III.
Any help please?
Thanks,
Cruise
data have;
input codes $ valid_flag $;
datalines;
0D7Q8ZZ No
XHRPXL2 No
0BDN4ZZ No
123456788 No
23456 CAT1
234 No
0090T CAT3
0987F CAT2
HYDHDJH No
;
if lengthn(strip(codes)) gt 5 then CPT5=0; else CPT5=1;
if 99201<=codes<=99499 then cpt_code= 'management';
if 00100<=codes<=01999 then cpt_code= 'anasthesia';
if 10021<=codes<=69990 then cpt_code= 'surgery';
if 70010<=codes<=79999 then cpt_code= 'radiology';
if 80047<=codes<=89398 then cpt_code= 'pathology_lab';
if 90281<=codes<=99607 then cpt_code= 'medicine';
if '0500F'<=codes<='0503F' then cpt_code= 'cat2';
The regular expressions would be:
data have;
input codes $ valid_flag $;
if prxMatch("/^\d{5}\s*$/o",codes) then flag = "CAT1";
else if prxMatch("/^\d{4}F\s*$/o",codes) then flag = "CAT2";
else if prxMatch("/^\d{4}T\s*$/o",codes) then flag = "CAT3";
else flag = "No";
datalines;
0D7Q8ZZ No
XHRPXL2 No
0BDN4ZZ No
123456788 No
23456 CAT1
234 No
0090T CAT3
0987F CAT2
HYDHDJH No
;
The regular expressions would be:
data have;
input codes $ valid_flag $;
if prxMatch("/^\d{5}\s*$/o",codes) then flag = "CAT1";
else if prxMatch("/^\d{4}F\s*$/o",codes) then flag = "CAT2";
else if prxMatch("/^\d{4}T\s*$/o",codes) then flag = "CAT3";
else flag = "No";
datalines;
0D7Q8ZZ No
XHRPXL2 No
0BDN4ZZ No
123456788 No
23456 CAT1
234 No
0090T CAT3
0987F CAT2
HYDHDJH No
;
I'm trying to add in other rules like:
else if prxMatch("/^\d{1}V\s*$/o",codes) then flag = "VCODE"; /*if initialized with letter V followed with numbers then VCODE*/
else if prxMatch("/^\s*$/o",codes) then flag = "ALLCHACR"; /*if codes have nothing but letters then ALLCHAR*/
where am i making mistakes in modifying your code above?
Thanks in advance.
I recommend reading the regex-documentation: http://support.sas.com/documentation/cdl/en/lefunctionsref/67398/HTML/default/viewer.htm#p0s9ilagexm...
Edit:
The regex for VCODE could be /^V\d+\s*$/
and ALLCHARS is /^[a-z]+\s*$/
Since all alphanumeric vars are filled with blanks, you have to add \s*.
My solution uses the regex posted by @PGStats. Using formats could be less efficient, but if you have more expressions to check, testing them is easier and they can be used in other programs without duplicating them.
proc format;
invalue $Cat1Test
"/^\d{5}\s*$/" (regexp) = 'CAT1'
other = [$Test2Cat.]
;
invalue $Test2Cat
"/^\d{4}F\s*$/" (regexp) = 'CAT2'
other = [$Test3Cat.]
;
invalue $Test3Cat
"/^\d{4}T\s*$/" (regexp) = 'CAT3'
other = "No"
;
run;
data have;
length codes $ 20 valid_flag check_flag $ 4;
input codes $ valid_flag $;
check_flag = input(codes, $Cat1Test.);
datalines;
0D7Q8ZZ No
XHRPXL2 No
0BDN4ZZ No
123456788 No
23456 CAT1
234 No
0090T CAT3
0987F CAT2
HYDHDJH No
;
run;
Thanks. SAS documentation helped. And I got it for V and ECODES and all non-digits like "TYUIO".
data have;
input codes $;
cards;
12345
123456
1234F
1234T
123
V1234
E1234
TYUIO
;
run;
data have1; set have;
length flag $8;
if prxMatch("/^\d{5}\s*$/o",codes) then flag = "CAT1";
else if prxMatch("/^\d{4}F\s*$/o",codes) then flag = "CAT2";
else if prxMatch("/^\d{4}T\s*$/o",codes) then flag = "CAT3";
else if prxMatch("/^V\d{4}\s*$/o",codes) then flag = "VCODE";
else if prxMatch("/^E\d{4}\s*$/o",codes) then flag = "ECODE";
else if prxMatch("/^\D{5}\s*$/o",codes) then flag = "NON_DIG";
RUN;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.