PROC SQL update errors

[SASsy05]

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

[JBailey]

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; 

[SASsy05]

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

[ballardw]

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 

[SASsy05]

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.

[JBailey]

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

[SASsy05]

Thanks! I will play around with this a bit.

[JBailey]

Hi @SASsy05

Any news?

[SASsy05 · Accepted solution]

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.