BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SASsy05
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
SASsy05
Obsidian | Level 7

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.

View solution in original post

8 REPLIES 8
JBailey
Barite | Level 11

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
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

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
Barite | Level 11

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
Obsidian | Level 7

Thanks! I will play around with this a bit.

JBailey
Barite | Level 11

Hi @SASsy05

 

Any news?

SASsy05
Obsidian | Level 7

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 3293 views
  • 0 likes
  • 3 in conversation