topic Re: SQL Update ignoring the firstobs and obs statements. in SAS Procedures

SQL Update ignoring the firstobs and obs statements.

CurtisMack — Thu, 22 Oct 2009 22:15:02 GMT

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

Re: SQL Update ignoring the firstobs and obs statements.

DanielSantos — Fri, 23 Oct 2009 08:28:28 GMT

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

Re: SQL Update ignoring the firstobs and obs statements.

LinusH — Fri, 23 Oct 2009 08:39:52 GMT

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

Re: SQL Update ignoring the firstobs and obs statements.

CurtisMack — Fri, 23 Oct 2009 18:23:35 GMT

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!