10-20-2016 03:11 PM
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.....
I am using SAS enterprise Guide 7.1
10-20-2016 09:07 PM
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;