Help using Base SAS procedures

SQL Update ignoring the firstobs and obs statements.

Reply
Frequent Contributor
Posts: 102

SQL Update ignoring the firstobs and obs statements.

Hi all,

Here is some behaviour I didn't expect:

proc sql ;
create table junk as
select * from NewPremises(firstobs = 5 obs=5);
update NewPremises(firstobs = 5 obs=5) set PremisesId = 99;
quit;
----------------------------------
9185 proc sql ;
9186 create table junk as
9187 select * from NewPremises(firstobs = 5 obs=5);
NOTE: Table WORK.JUNK created, with 1 rows and 8 columns.

9188 update NewPremises(firstobs = 5 obs=5) set PremisesId = 99;
NOTE: 52798 rows were updated in WORK.NEWPREMISES.

9189 quit;
-------------------------------------------

Why does the Update statement ignore the FIRSTOBS and OBS statment and update the entire dataset, but the select statement works as I expected?

Also, does anybody know of a way to update a row based on it's row number?

I appreciate any insight you may have.
Curtis
Super Contributor
Posts: 474

Re: SQL Update ignoring the firstobs and obs statements.

Posted in reply to CurtisMack
FIRSTOBS and OBS are input options (read).

See the DETAILS section of the online doc:
http://support.sas.com/documentation/cdl/en/lrdict/62618/HTML/default/a000131129.htm

Cheers from Portugal.

Daniel Santos @ www.cgd.pt
Super User
Posts: 5,437

Re: SQL Update ignoring the firstobs and obs statements.

Posted in reply to DanielSantos
The only efficient way to update y row number in SAS as far as I know is using the data step, MODIFY and POINT.

/Linus
Data never sleeps
Frequent Contributor
Posts: 102

Re: SQL Update ignoring the firstobs and obs statements.

Thanks for both of your replies. It confirms my suspisions that there is no way to do this in PROC SQL. I use MODIFY in most of my DB processing, but in the particular solution I am working on, I am stuck in SQL. I have found an inellegant solution that I will just have to live with.
Thanks Again!
Ask a Question
Discussion stats
  • 3 replies
  • 181 views
  • 0 likes
  • 3 in conversation