Date:         Thu, 23 Apr 2009 17:25:07 -0500
Reply-To:     Joe Matise <snoopy369@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Joe Matise <snoopy369@GMAIL.COM>
Subject:      Re: sas update statemet
Comments: To: Jeff <zhujp98@gmail.com>
In-Reply-To:  <6716d5d0904231417s59d6b743r6f0af773442c7a30@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1

You need to use a WHERE statement, not a FROM statement.

General syntax of UPDATE:

UPDATE table (tbl)
SET (whatever) = (whatever)
WHERE (condition is true)

so it would presumably be something like

UPDATE table Diagnosis s
SET icd9category=(select t.icd9category from diagcat t where s.icd9code =
t.ex_icd9diagcode)
WHERE exists (select 1 from diagcat t where s.icd9code = t.ex_icd9diagcode);

You do the 'exists' part (the same WHERE clause twice) in order to only
update those rows where you have a value.  This requires there to only be a
unique match on table diagcat, by the way, so it will error if that's not
true- either use SELECT DISTINCT or MIN() or MAX() or somehow otherwise
obtain a unique value in the select statement.

(Untested as I don't have anything to test it on but that's the theory).

-Joe

On Thu, Apr 23, 2009 at 4:17 PM, Jeff <zhujp98@gmail.com> wrote:

> * How to fix this code?
> Thanks.
> Jeff
>
> proc* *sql* noprint;
>
> update table Diagnosis
>
> set ICD9Category = t.ICD9Category
>
> from Diagnosis s
>
> join DiagCat t
>
> on s.icd9code = EX_ICD9DiagCode;
> * quit*;
>
>
> proc sql noprint;
>
> 461 update table Diagnosis
>
> 462 set ICD9Category = t.ICD9Category
>
> 463 from Diagnosis s
>
> ----
>
> 22
>
> 76
>
> ERROR 22-322: Syntax error, expecting one of the following: ;, !!, *, **,
> +,
> ',', -, /, WHERE,
>
> ||.
>
> ERROR 76-322: Syntax error, statement will be ignored.
>
> 464 join DiagCat t
>
> 465 on s.icd9code = EX_ICD9DiagCode;
>
> 466 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
>