SQL with update and select

Reply
Super Contributor
Super Contributor
Posts: 444

SQL with update and select

hi,

i am populating a table q2 with variables from another table mylib.pvl_source with this code below  (only pseudo is a common variable for both tables)

proc sql;

update q2

set vdt_base,vd_base=(select coldate,pviral from mylib.pvl_source a where q2.pseudo=a.pseudo and

coldate>=add_months(farvdt,-6) and coldate<farvdt);quit;


and i am getting this error which i have no clue how to fix.Any suggestion please?


1102  proc sql;

1103  update q2

1104  set vdt_base,vd_base=(select coldate,pviral from mylib.pvl_source a where

                  -

                  73

                  76

1104! q2.pseudo=a.pseudo and

ERROR 73-322: Expecting an =.

ERROR 76-322: Syntax error, statement will be ignored.

1105  coldate>=add_months(farvdt,-6) and coldate<farvdt);

1105!                                                    quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

Super User
Posts: 19,792

Re: SQL with update and select

remove vdt_base from the set statement. 

Super Contributor
Super Contributor
Posts: 444

Re: SQL with update and select

but then vdt_base, wont appear in table q2.Sorry, what difference would removing vdt_base make?

Super User
Posts: 19,792

Re: SQL with update and select

an update leaves the tables as they are and only updates the fields so if the field is already there then it stays there.

If you want to add a new field, you first need to add the column then update it, using set statement.

Super User
Posts: 10,028

Re: SQL with update and select

You can only handle one variable one time.

And don't forget using WHERE when you update.

vdt_base=(select ..),vd_base=(select ..)


  where .......

Ksharp

Super Contributor
Super Contributor
Posts: 444

Re: SQL with update and select

"You can only handle one variable one time",... so is that only for SAS/SQL?

So now i ve tried with one variable at a time

proc sql;
update mylib.q2 q
set vdt_base=(select coldate from mylib.pvl_source a where q.pseudo=a.pseudo and
coldate>=add_months(farvdt,-6) and coldate<farvdt),vd_base=(select pviral from mylib.pvl_source a where q.pseudo=a.pseudo and coldate>=add_months(farvdt,-6) and coldate<farvdt);
quit;

But now it looks like SAS does not recognize "add_months"

ERROR: Function ADD_MONTHS could not be located.
ERROR: Expression using greater than or equal (>=) has components that are of different data
       types.

Respected Advisor
Posts: 4,921

Re: SQL with update and select

Tal, if it isn't in SAS documentation, it probably isn't in SAS. Instead of

add_months(farvdt,-6)

use

intnx("MONTH", farvdt, -6, "SAMEDAY")

Check function INTNX in the documentation for other options.

PG

PG
Super Contributor
Super Contributor
Posts: 444

Re: SQL with update and select

Thanks PG.

It is annoying how Oracle SQL and SAS SQL have different approach for some of the things.

I did try with this function intnx() and now i am getting another error

ERROR: Subquery evaluated to more than one row.

New Contributor
Posts: 4

Re: SQL with update and select

Tal, that's because the uncorrelated subquery you are using in the set statement returns more than a single value but not using the operators that can handle multiple values(like IN, EXISTS etc.). Try something like this:

proc sql;

update mylib.q2 q

set vdt_base=

(select a.coldate from mylib.pvl_source a where q.pseudo=a.pseudo and coldate>=intnx("MONTH", farvdt, -6, "SAMEDAY") and coldate<farvdt)

,

vd_base=

(select a.pviral from mylib.pvl_source a where q.pseudo=a.pseudo and coldate>=intnx("MONTH", farvdt, -6, "SAMEDAY") and coldate<farvdt)

where exists

(select a.coldate, a.pviral from mylib.pvl_source a where q.pseudo=a.pseudo and coldate>=intnx("MONTH", farvdt, -6, "SAMEDAY") and coldate<farvdt)

;

quit;

Ask a Question
Discussion stats
  • 8 replies
  • 346 views
  • 3 likes
  • 5 in conversation