Hi,   I am trying to update a postgres table using proc sql update. I have 2 tables: db.drug_exposure is the table that I am trying to update from the second table dtext.txtmatch. I want drug_concept_id in db.drug_exposure to be updated with drug_cid from dtext.txtmatch when drug_exposure_id = drug_exposure_id. I have tried the following, all producing at least one error (SAS log with errors below the code).   Thank you in advance for any help!   proc sql; update db.drug_exposure as t1 set drug_concept_id = t2.drug_cid, From dtext.txtmatch as t2 Where t1.drug_exposure_id=t2.drug_exposure_id; quit; proc sql; update db.drug_exposure t1 set drug_concept_id = (select drug_cid from dtext.txtmatch t2) Where t2.drug_exposure_id=t1.drug_exposure_id; quit; proc sql; update db.drug_exposure set drug_concept_id = (select drug_cid from dtext.txtmatch) Where drug_exposure_id in (select drug_exposure_id from dtext.txtmatch); quit; proc sql; update db.drug_exposure set drug_concept_id = drug_cid, from dtext.txtmatch where drug_exposure_id = drug_exposure_id; quit;     2827 2828  proc sql; 2829      update db.drug_exposure as t1 2830      set drug_concept_id = t2.drug_cid, 2831      From dtext.txtmatch as t2                -----          --                73             22                               76 ERROR 73-322: Expecting an =. ERROR 22-322: Syntax error, expecting one of the following: ;, !!, *, **, +, ',', -, /, WHERE,               ||. ERROR 76-322: Syntax error, statement will be ignored. 2832      Where t1.drug_exposure_id=t2.drug_exposure_id; 2833  quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time):       real time           0.01 seconds       cpu time            0.01 seconds 2834 2835 2836  proc sql; 2837    update db.drug_exposure t1 2838    set drug_concept_id = 2839          (select drug_cid 2840          from dtext.txtmatch t2) 2841              Where t2.drug_exposure_id=t1.drug_exposure_id; ERROR: Unresolved reference to table/correlation name t2. ERROR: Expression using equals (=) has components that are of different data types. 2842  quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time):       real time           0.06 seconds       cpu time            0.00 seconds 2843 2844  proc sql; 2845    update db.drug_exposure 2846    set drug_concept_id = 2847          (select drug_cid 2848          from dtext.txtmatch) 2849              Where drug_exposure_id in (select drug_exposure_id from dtext.txtmatch); ERROR: Subquery evaluated to more than one row. 2850  quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time):       real time           0.09 seconds       cpu time            0.01 seconds 2851 2852 2853  proc sql; 2854      update db.drug_exposure 2855      set drug_concept_id = drug_cid, 2856      from dtext.txtmatch                -----                73 ERROR 73-322: Expecting an =. 2857  where drug_exposure_id = drug_exposure_id; 2858  quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time):       real time           0.01 seconds       cpu time            0.01 seconds ... View more

Hi,   I am trying to use hash tables to attach a numeric value when a certain drug name is found.   I have 2 tables -      Table 1: drug_name_1, drug_name_2, drug_name_3 and drug_cid; some records only have drug_name_1 populated, some have drug_name_1 and drug_name_2, and some have all three. All records have drug_cid.        Table 2: x_drug_name, drug_concept_id, (and some others not important to this at the moment). I am trying to find the ones that have drug_concept_id=0 and match the text terms in x_drug_name to the terms in Table 1 and assign a drug_cid to this record.   This is what I want to do: 1) Match if drug_name_1, drug_name_2 AND drug_name_3 are present in the field x_drug_name and assign drug_cid. 2) Match if drug_name_1 AND drug_name_2 are present in the field x_drug_name and assign drug_cid. 3) Match if only drug_name_1 is present in the field x_drug_name and assign drug_cid.   The below code is me attempting #1 above. I was going to run through this 3 times each with one less drug_name and removing the ones that matched at each step from each subsequent run. I think it is having problems because some of the records are missing drug_name_2 and/or drug_name_3. I am restricted to Table 1 being set up this way.   Any ideas on how to make this work?   Thanks for any and all suggestions!   data MatchCombo_a; length x_drug_name $100 drug_name_1 $50 drug_name_2 $50 drug_name_3 $50; If (_n_=1) then do; declare hash Drug_c (dataset: 'Drugs.med_statin'); Drug_c.DefineKey ('drug_name_1', 'drug_name_2', 'drug_name_3'); Drug_c.DefineData ('drug_name_1', 'drug_name_2','drug_name_3', 'drug_cid'); Drug_c.DefineDone (); call missing(drug_name_1, drug_name_2, drug_name_3, drug_cid); end; do until (eof1) ; set sample.de_sample end = eof1; where drug_concept_id=0; x_drug_name2=x_drug_name; x_drug_name3=x_drug_name; do i = 1 to 5; drug_name_1=scan(upcase(x_drug_name),i,' -/!*#\,'); do j = 1 to 5; drug_name_2=scan(upcase(x_drug_name2),j,' -/!*#\,'); do k = 1 to 5; drug_name_3=scan(upcase(x_drug_name3),k,' -/!*#\,'); rc = Drug_c.find(key: drug_name_1, key: drug_name_2, key: drug_name_3); if rc=0 then output; End; End; End; End; ... View more

Hello,   I have been working on the following for quite some time, and I just can't seem to figure it out.   I have 2 tables -      Table 1: ingredient_1, ingredient_2 and drug_cid;      Table 2: x_drug_name, drug_concept_id, (and some others not important to this at the moment)   I want to match ingredient_1 AND ingredient_2 from Table 1 to x_drug_name in Table 2 when drug_concept_id=0. I am doing a text matching. x_drug_name might look something like this:  "amLODIPine-atorvastatin 5 mg-20 mg oral tablet". When there is a record where both ingredient_1 and ingredient_2 are present, I want to add drug_cid to the record.   Here is my code:   data TxtMatch; length x_drug_name $ 50 ingredient_1 $50 ingredient_2 $50; If (_n_=1) then do; declare hash drug (dataset: 'Drugs.statincombos'); Drug.DefineKey ('ingredient_1', 'ingredient_2'); Drug.DefineData ('ingredient_1', 'ingredient_2','drug_cid'); Drug.DefineDone (); call missing(ingredient_1, ingredient_2, drug_cid); end; do until (eof2) ; set sample.de_sample2 end = eof2; where drug_concept_id=0; x_drug_name2=x_drug_name; do i = 1 to 5; ingredient_1=scan(upcase(x_drug_name),i,' -/!*#\,'); do i = 1 to 5; ingredient_2=scan(upcase(x_drug_name2),i,' -/!*#\,'); rc = Drug.find(key: 'ingredient_1', key: 'ingredient_2'); if rc=0 then output; End; End; End; keep drug_exposure_id drug_exposure_start_date x_drug_name x_drug_name2 ingredient_1 ingredient_2 drug_concept_id drug_cid; run;   I am not getting any errors, so I am not sure where the problem is.   Thank you for any help and guidance you may be able to provide     --SS ... View more