Match to identify using select codes

[Cruise]

I have N=36 million rows of data where 36K unique patients (pat_id) are associated with multiple drugs (drug_codes). See mock data below. I want to identify select 15-20 drugs in the 'patient_file' by matching on 'drug_codes'. Code blocks below is to show my attempts, unfortunately, none of them worked.

data patient_file;
input pat_id drug_codes $;
cards;
1 555130206
2 000033734
3 648421020
3 000027669
4 504190171
4 000245840
5 555130954
6 000240590
7 502420060
7 667330948
8 000041100
8 000091111
9 001873204
9 441390019
10 000063026
;

data drugs_identified; set patient_file; 
if find(drug_codes,'555130206','i') ge 1 then flag=1; 
if find(drug_codes,'000033734','i') ge 1 then flag=1; 
if find(drug_codes,'648421020','i') ge 1 then flag=1; 
if flag=1;
run;

data drugs_identified1; set patient_file; 
if drug_codes eq '555130206' then flag=1; 
if drug_codes eq '000033734' then flag=1; 
if drug_codes eq '648421020' then flag=1; 
if flag=1;
run;

data drugs_identified2; set patient_file; 
if drug_codes in '555130206' then flag=1; 
if drug_codes in '000033734' then flag=1; 
if drug_codes in '648421020' then flag=1; 
if flag=1;
run;    

 

I'm baffled! Any help is appreciated. Thanks for your precious time indeed!

Do you know why using Insert SAS Code option flattens my code to one line? for future reference.

[bobpep212]

See your input statement. Your drug_codes are all 9 characters long, but you are creating a column with the default length of 8 by only using the $.  Try: input pat_id drug_codes $9.; (assuming they are always 9 chars). 

Then I did a PROC SQL to keep only those patient_ids where they drug codes are in your list like this:

proc sql;
create table drug_codes as (
select pat_id, drug_codes 
from patient_file
where drug_codes in ('555130206', '000033734' '648421020')
)
;
quit;

 Also, not certain why it flattened your code.  Hope this solved it!

[Reeza]

---

@bobpep212 wrote:

 Also, not certain why it flattened your code.  Hope this solved it!

---

It's an annoying bug with the editor. If you're in the editor window (code editor) and enter everything in there before coming back to the Rich Text editor it's fine. If you enter and then modify after in the Rich Text Editor it breaks all the line breaks for some reason. I don't know why it happens, but I know it happens ¯\_(ツ)_/¯. I try and fix it when I see it happening, you can copy/paste your code into EG, hit CTRL+I to get it formatted and paste back out. Or SAS Studio has a format code button itself.

[Astounding · Accepted solution]

The style of INPUT that you are using will define DRUG_CODES as 8 characters long.  To get around that, insert this statement before the INPUT statement:

length drug_codes $ 9;

[Cruise]

You guys are awesome. Thanks!