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
I ended up creating a new table, deleting the old table in pgAdmin and loading the new table with the updated data back in. Another problem that arose, was that even though I deleted the original table, the new table still had to have every single variable as the old table for it to load back in.
Hi @SASsy05,
Try removing the comma after drug_cid. I have taken it out of the code, below.
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;
Thanks, but this is the error I get now.
2866 proc sql;
2867 update db.drug_exposure as t1
2868 set drug_concept_id = t2.drug_cid
2869 From dtext.txtmatch as t2
---- --
22 76
202
ERROR 22-322: Syntax error, expecting one of the following: ;, !!, *, **, +, ',', -, /, WHERE,
||.
ERROR 76-322: Syntax error, statement will be ignored.
ERROR 202-322: The option or parameter is not recognized and will be ignored.
2869! From dtext.txtmatch as t2
--
22
ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, <, <=,
<>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET,
LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.
2870 Where t1.drug_exposure_id=t2.drug_exposure_id;
2871 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds
First error:
2830 set drug_concept_id = t2.drug_cid,
the comma says something is separated but is in a place not expected
proc sql; update db.drug_exposure as t1 set drug_concept_id = (select t2.drug_cid From dtext.txtmatch as t2 Where t1.drug_exposure_id=t2.drug_exposure_id); quit;
may be a bit closer to what you want.
Next you have ) in the wrong place
That was actually the first one I tried. The original postgres table has a non-null consraint on drug_concept_id and I believe what is happeneing is that my table (dtext.textmatch) only contains some records and not all, but it is trying to update all records, so I am getting the following error.
2872 proc sql;
2873 update db.drug_exposure as t1
2874 set drug_concept_id = (select t2.drug_cid
2875 From dtext.txtmatch as t2
2876 Where t1.drug_exposure_id=t2.drug_exposure_id);
ERROR: Error updating table entry: ERROR: null value in column "drug_concept_id" violates not-null
constraint Failing row contains (null, 2014-11-04, 2014-11-04, 0, -1, null,
null, null, three times daily as needs, null, null, 0, Atarax 25 mg Tabs, ODA, Atarax 25 mg
Tabs,).; Error while executing the query
ERROR: ROLLBACK issued due to errors for data set DB.DRUG_EXPOSURE.DATA.
Hi @SASsy05
I didn't use your exact table names (I should have) but I did create what I think is a working example. If your tables are very large it may help to use explicit pass-through for the UPDATE statement. The example below uses only SAS (no PostgreSQL). The EXISTS predicate is important and stops a very sneaky side effect. It may help to remove it and see what happens. Here you go.
proc sql;
create table drug_exposure (drug_exposure_id int,
drug_concept_id int);
insert into drug_exposure values (1,10);
insert into drug_exposure values (2,20);
insert into drug_exposure values (3,30);
insert into drug_exposure values (5,50);
create table drug_txtmatch (drug_exposure_id int,
drug_cid int);
insert into drug_txtmatch values (1,11);
insert into drug_txtmatch values (2,22);
insert into drug_txtmatch values (3,33);
insert into drug_txtmatch values (4,44);
quit;
proc sql;
update drug_exposure as t1
set drug_concept_id = (select t2.drug_cid
From drug_txtmatch as t2
Where t1.drug_exposure_id=t2.drug_exposure_id)
where exists (select 1
from drug_txtmatch as t3
where t1.drug_exposure_id = t3.drug_exposure_id);
quit;
Hope this helps.
Best wishes,
Jeff
Thanks! I will play around with this a bit.
I ended up creating a new table, deleting the old table in pgAdmin and loading the new table with the updated data back in. Another problem that arose, was that even though I deleted the original table, the new table still had to have every single variable as the old table for it to load back in.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.