Thank you RW9. Basically I am just trying to get a dataset with all patients that have a record of the drugs specified and leave the rest of the unspecified drugs behind. ... View more

Hi I am not sure the above would work as they are all different drugs in the % let statement. and in the datastep you are still calling out each drug e..g. ABARELIX.  What i am ultimately trying to do is not repeat the same datastep for every single drug type as there are over 100 so for example how can i  get the following three steps in to one step that calls the drug name as a macro variable. I am trying to make a macro out of the following statement.  Here we just have two drugs so don't need a macro but in my program there are over a 100 drugs.  I want to put the drugs in a let statement to call throughout my program. Currently data x ;   set y;   if  index(upcase(GENERIC_NAME),'drug_a')>0 or                                          index(upcase(GENERIC_NAME),'drug_b')>0) ; run; Trying to change it to: %let drug=('drug_a' 'drug_b'); data x;   set y; if index(upcase(GENERIC),&drug) >0 ; run; but i get the error:                     ERROR 388-185: Expecting an arithmetic operator. ERROR 76-322: Syntax error, statement will be ignored. ... View more

do you have any suggestions on the best way to do this using proc tabulate? ... View more

Hi here is the code I used and it worked perfectly thank you so much for your help.  proc sql;   create table Preg_GLP as select a.*, b.index_date, b.PatBegDate, b.PatEndDate from  Patient_Medication_GLP a left join Patient_Summary_Preg  b on a.PATID = b.PATID; quit; ... View more

PATID would be the same so for example I would have two pregnancies in the PREG dataset both 101.  I would then have 7 therapies in the THERAPY dataset all with PATID 101.  So in the merged final dataset I would basically have the therapies duplicated for each pregnancy event.  Many thanks, ... View more

Thanks so much Peter for your help I have updated my program with your improved suggestions.  I have a quick question about the  code below.  Is it possible to adapt it to look for ranges that have a character in them as well say 100E-150E or V300-V400.  at the moment I am stripping the prefix and suffix out in the Excel file and running the statement below and then concatenating the Character back on in another datastep.  Many thanks again for your brilliant answer. data Code_Range1 ;      set Code_Range ;      Begin_code = scan( code,1) ;        end_code = scan( code, -1) ;      len_indiv =max(length( begin_code), length(end_code) ) ; do Indiv_C= Begin_Code to End_code; Length indiv_code $15 ;         Indiv_code = putn( indiv_c, 'Z', len_indiv ) ;           output ;      end; Drop begin_code end_code len_indiv indiv_c ; run; ... View more

Brilliant thank you EJ!! ... View more

Unfortunately the variable code is differing in length and number of leading zeros.  Would z8. not make code 8 characters long for every instance i have a variety of codes 050 50 0050 etc and the number of leading zeros is very important it has to be exact. ... View more

Hi Amir, Sorry for my delay in getting back to you.  Here is my program as it stands.  I just wanted to know if I could "marcrofy" some bits of it as it quote long winded. *****Notes ***Import Excel worksheet contain code ranges eg - Excel Column called  CODE                                                                                                                                                           001-010                                                                                                                                                           050-055                                                                                                                                                           1000-1100    and so on. libname Codes excel "Codelist_LH.xlsx"   header = yes   mixed = yes; run; data Code_Range;   set Codes.'List$'n;   where Lvl1Group ne 'Exclusion'; run; *****Read the Variable containing the individual code or code range that has been imported  and list out each code individually in a new Variable called INDIV_CODE; data Code_Range1 (drop=BeginCode EndCode);      set Code_Range ;      BeginCode = input(scan(Code,1),best10.);      EndCode = input(scan(Code,2),best10.);       if missing(EndCode) then do Indiv_Code=input(Code,best10.);output ;end;         else do Indiv_Code = BeginCode to Endcode;           output ;      end; run; *****Identify number of leading Zeros associated with original variable CODE to later assign to numeric variable INDIV_CODE ; Data Code_Zero;     set Code_Range1;      Check_for_005=find(code,'00000');      Check_for_004=find(code,'0000');      Check_for_003=find(code,'000');      Check_for_002=find(code,'00');      Check_for_001=find(code,'0'); run; *****Take the correct number of leading zeros per code and create a variable CODEFINAL containing the correct number of zeros ; data Code_Zero1;     set Code_Zero;      if Check_for_001=1 and Check_for_002=0 and Check_for_003=0 and Check_for_004=0 and Check_for_005=0 then do Codefinal=substr(Code,1,1.);end;else;      if Check_for_002=1 and Check_for_003=0 and Check_for_004=0 and Check_for_005=0 then do Codefinal=substr(Code,1,2.);end;else;      if Check_for_003=1 and Check_for_004=0 and Check_for_005=0 then do Codefinal=substr(Code,1,3.);end;else;      if Check_for_004=1 and Check_for_005=0 then do Codefinal=substr(Code,1,3.);end; run; *****Concatenate the variable CODEFINAL (contains zeros) and INDIV_CODE contains original code minus leading zeros; data Num_Code (drop= check_for_001 check_for_002 check_for_003 check_for_004 check_for_005 Indiv_Code codefinal) ;     set Code_Zero1;      Numcode=trim(left(codefinal))||trim(left(Indiv_Code)); run; *****Concatenate Character Prefix or Suffix to the variable Numcode ; data Char_Code;     set Num_Code;      if missing(Prefix_) and missing(Suffix) then do Complete_Code=Numcode;end; else;      if Prefix_ ne " " then do Complete_Code=trim(left(Prefix_))||trim(left(Numcode));end; else      if Suffix ne " " then do Complete_Code=trim(left(Numcode))||trim(left(Suffix));end; Run; *****Drop Prefix and Suffix Columns and rename Complete_Code; Data Final_Code (drop=numcode prefix_ suffix Complete_Code);   set Char_Code;   code= Complete_Code; Run; ... View more