DATA Step, Macro, Functions and more

Match to identify using select codes

Accepted Solution Solved
Reply
Super Contributor
Posts: 365
Accepted Solution

Match to identify using select codes

[ Edited ]

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.

 

 


Accepted Solutions
Solution
4 weeks ago
Super User
Posts: 6,781

Re: Match to identify using select codes

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;

View solution in original post


All Replies
Occasional Contributor
Posts: 11

Re: Match to identify using select codes

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!

Super User
Posts: 23,754

Re: Match to identify using select codes

Posted in reply to bobpep212

@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.

Solution
4 weeks ago
Super User
Posts: 6,781

Re: Match to identify using select codes

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;

Super Contributor
Posts: 365

Re: Match to identify using select codes

Posted in reply to Astounding

You guys are awesome. Thanks!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 80 views
  • 4 likes
  • 4 in conversation