Need Help using Like or contains functions in PROC SQL routine

[BrianConner]

All, I am new to SAS and I am having a problem using a like or contains function in my routine.

Below is my code

 PROC SQL;
 CREATE TABLE FINAL AS

 SELECT DISTINCT
        T1.*,
        CASE WHEN T2.NPI IS NOT NULL THEN 'Preventative Visit' ELSE '' END AS CATEGORY,
		DOS,
        CASE WHEN T3.HCC IS NOT NULL THEN T3.HCC ELSE 0000 END AS CATEGORY
 FROM WORK.ALLDATA T1
 LEFT JOIN CLAIMS2 T2 ON T2.mbr_key = T1.mbr_key AND T1.NPI = T2.NPI
 LEFT JOIN CLAIMS_HCC T3 ON T2.mbr_key = T1.mbr_key AND T1.ALL_HCC contains '',' || T3.HCC || ',''

 ;
 RUN;

1.  the field T1.ALL_HCC has data that looks like this example: 81,22,100 (basically it can contain one or multiple numbers seperated by commas) the data type is string for this field.

2. the field T3.HCC will only contain one number

 In my Case statement I want to return the T3.HCC when it exists anywhere in field T1.ALL_HCC

Any help is greatly appreciated.....

Brian

I am using SAS enterprise Guide 7.1

[DarthPathos]

I think this will give you what you need (dummy data used, but I think you'll get the idea :-)).

First, split the put the t3.hcc variable into a new table by itself as this will make it easier.  The first data step is creating my "base table" where I want to search for the value in the first column.  The second data step creates my "output" from whatever data source generated the data. I'm using an INNER JOIN because I want only the rows where everything matches, and i'm using the wildcard '%' as a way to build my search parameters.  I'm hoping this makes sense - i've had a super busy day and playing around with SAS is my way of relaxing, but communicating is not my strength right now LOL 🙂  

I'm using SAS University Edition at home right now, so you should be fine running this in Base SAS or EG, but if you have any questions holler back and I'll fiddle with it at work tomorrow.  

Good luck, and let me know how you make out.  Happy to share resources etc. if you're interested!

DATA work.test;
 INPUT charvar1 $3.
 +1 charvar2 $1.
 +1 numvar1
 +1 numvar2 DATE7.
 ;
DATALINES;
me1 F 35786 10oct50
me3 M 57963 25jun49
fg6 M 25754 17jun47
fg7 F . 17aug53
;

DATA work.test2;
input charvar1 $20.
;
datalines;
m1 m3 me2 m3 d3 d3
d2 f5 g7 jd4 dk3 lk3
me1 
mr4 k9 k8 jd8 kj8
;

proc sql;
SELECT a.*
FROM work.test a inner join work.test2 b on a.charvar1 = b.charvar1
WHERE a.charvar1 like '%' & b.charvar1 & '%';
quit;