An sql procedures doesn't work?

Reply
Occasional Contributor
Posts: 8

An sql procedures doesn't work?

[ Edited ]

Hello everybody.

So I am trying to run this SQL statement (runs in SQL server smoothly) in SAS. I would highly appreciate it if somebody could point me at the problem why doesn't it work:

proc sql UNDO_POLICY=NONE;

update a set

a.var1=b.var2

from table1 a inner join table2 b on a.[month]=b.month1 and a.var3=b.var4

where a.var5='bla';

run;

 

 

 

The corresponding error is "Expecting an =." (at the line a.var1=b.var2)

 

Thank you in advance.

Marin

 

Super User
Posts: 3,105

Re: An sql procedures doesn't work?

Remove the square brackets ([]). These don't work in SAS SQL, only SQL Server

Occasional Contributor
Posts: 8

Re: An sql procedures doesn't work?

Thank you for express answer. However, this does not solve my problem, i.e. the error produced is unchanged.

Thanks again.

Super User
Posts: 3,105

Re: An sql procedures doesn't work?

Also you can only do updates like this - for each variable you update you need a separate select sub-query:

 

proc sql;
  update want
  set var1 = (select var2 from have);
  ;
quit;

 

Occasional Contributor
Posts: 8

Re: An sql procedures doesn't work?

Hello Kiwi, I appreciate your help but my code still produces the same error. My querry now looks like:

 

proc sql UNDO_POLICY=NONE;

update a set

a.var1 = (select b.var2

from table1 a inner join table2 b on a.month=b.month1 and a.var3=b.var4

where var5='bla');

run;

 

Thanks!

Super User
Posts: 17,819

Re: An sql procedures doesn't work?

Perhaps check the doc for format you need?

 

update table1 as

Set var1= (select b.var2 from table2 as b where b.id=table1.id and ... );

quit;

Occasional Contributor
Posts: 8

Re: An sql procedures doesn't work?

Thanks a lot everybody and sorry for the delay. The problem isn't solved - I just can't run this.

The weird thing for me is that it runs in SQL server - does anybody know a good, short overview of SAS interpretor of SQL and how it differs from usual SQL?

Super User
Posts: 3,105

Re: An sql procedures doesn't work?

You may find it easier to start with some sample code that is known to work, then go from there:

 

http://support.sas.com/kb/25/455.html

 

In reality there are always differences in SQL syntax from different vendors, so its just a matter of learning them. If you are running your SQL on SQL Server you could use SQL passthru which will allow you to use SQL Server syntax unmodified.

Occasional Contributor
Posts: 8

Re: An sql procedures doesn't work?

Thanks!

Ask a Question
Discussion stats
  • 8 replies
  • 437 views
  • 0 likes
  • 3 in conversation