Date: Tue, 23 Jan 2007 11:46:11 -0500
Reply-To: "Richard A. DeVenezia" <rdevenezia@WILDBLUE.NET>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Richard A. DeVenezia" <rdevenezia@WILDBLUE.NET>
Subject: Re: table lookup / find string within table
"Miller, Jeremy T. (CDC/CCID/NCHHSTP) (CTR)" wrote:
> I have two data sets:
>
> "meds" is a patient history of meds containing hundreds of thousands
> of records on thousands of patients.
>
> "interest" is just a list of medications containing a few hundred
> records.
>
> I am interested in finding whether or not a patient was given a
> medication contained in "interest." Afterwhich, I would like to see
> how many times they were given the medication.
>
> My question: is there a more appropriate way to do this? My code
> works, but this seems to be a common situation and I think there must
> be a slicker way to accomplish the task. I've searched on hash
> lookups and table lookups, but I'm missing a key to search on.
>
> NOTE: All spellings are correct (i.e., I don't have to check for
> multiple spellings).
>
> I think there should be a way to count the occurances of each
> medication in the data step, but I can't figure it out.
>
> Thanks for any suggestions.
It is really a matter of the proper data structure and using Proc TABULATE.
SAS Procs offer Class and By processing, which require descriptor aspects of
data to be of a categorical nature.
This means have a column named MOI (med of interest) and one row per med per
patient per visit. Having umpteen columns whose names are the MOI values is
undesired.
In the sample:
SQL does an outer join and checks for 'usage' by seeing if a value of
interest is contained within a value of record.
Note the medications data for the new patient #3, it includes _two_
medications that are of interest.
The two meds of interest will each get a usage mark from the one patient
row.
data meds;
infile cards truncover;
length id visit_dt 8 medications $150;
format visit_dt mmddyy10.;
informat visit_dt mmddyy10.;
input id :visit_dt medications & $char150.;
cards;
1 1/1/2006 FERROUS SULFATE 325MG TAB
1 1/1/2006 ERYTHROMYCIN 250MG TAB
1 2/1/2006 IBUPROFEN 400MG TAB
1 2/1/2006 RANITIDINE 150MG TAB
1 2/1/2006 AZITHROMYCIN 250MG TAB (Z-PAK)
1 5/1/2006 PREDNISOLONE 1%/GENTAMICIN .3% OPH SUSP
1 6/1/2006 PREDNISOLONE 1% OPTH SUSP
1 7/1/2006 CEPHALEXIN 500MG CAP
1 7/1/2006 LEVOTHYROXINE 0.05MG TAB
1 7/1/2006 DIPHENHYDRAMINE 25MG CAP
1 9/1/2006 ERYTHROMYCIN 250MG TAB
1 10/1/2006 LEVOTHYROXINE 0.05MG TAB
1 10/1/2006 RANITIDINE 150MG TAB
1 11/1/2006 ESTROGEN/PROGEST. 0.625/2.5MG-PREMPRO
1 12/1/2006 AMITRIPTYLINE 25MG TAB
2 1/1/2006 ESTROGEN/PROGEST. 0.625/2.5MG-PREMPRO
2 2/1/2006 LEVOTHYROXINE 0.05MG TAB
2 3/1/2006 RANITIDINE 150MG TAB
2 4/1/2006 METHOTREXATE 2.5MG TAB
2 5/1/2006 ESTROGEN/PROGEST. 0.625/2.5MG-PREMPRO
2 6/1/2006 METHOTREXATE 2.5MG TAB
2 7/1/2006 METHOTREXATE 2.5MG TAB
2 7/1/2006 FOLIC ACID 1MG TAB
2 8/1/2006 ERYTHROMYCIN 250MG TAB
2 9/1/2006 FERROUS SULFATE 325MG TAB
2 10/1/2006 METHOTREXATE 2.5MG TAB
2 10/1/2006 SULFACETAMIDE 10% OPHTH SOL
2 11/1/2006 METHOTREXATE 2.5MG TAB
2 12/1/2006 ALBUTEROL INHALER
3 1/1/2006 ERYTHROMYCIN 250MG TAB _and_ METHOTREXATE 2.5MG TAB
;
run;
data interest;
input medpart $13.;
cards;
ACETAMINOPHEN
ERYTHROMYCIN
AMOXICILLIN
ATORVASTATIN
CLAVULANATE
ETODOLAC
GLIPIZIDE
GLYBURIDE
IBUPROFEN
INDOMETHACIN
ISONIAZID
KETOROLAC
LOVASTATIN
METFORMIN
METHOTREXATE
METHYLDOPA
NAPROXEN
NEFAZODONE
PHENYTOIN
ROSIGLITAZONE
SIMVASTATIN
TRAZODONE
TRIMETHOPRIM
VALPROATE
RANITIDINE
;
run;
proc sql;
create view usage as
select *
, upcase(meds.medications)
contains
upcase(interest.medpart)
as usage
from meds,interest
;
quit;
filename rpt temp;
ods html file=rpt style=meadow;
proc format;
value blankzero 0=' ';
run;
title "medication usage counts per patient";
proc tabulate data=usage;
class id medpart;
var usage;
table id,medpart=''*usage=''*sum=''*f=blankzero4.;
table medpart='',id*usage=''*sum=''*f=blankzero4.;
format usage blankzero.;
run;
ods html close;
--
Richard A. DeVenezia
http://www.devenezia.com/
