topic Re: Convert Update SQL statement to proc sql in SAS Programming

Convert Update SQL statement to proc sql

Ja5ya — Wed, 07 Oct 2020 04:21:15 GMT

Say I have 2 tables as following. What would be equivalent proc sql code to the update statement be?

create table #City (CityId varchar(20), [Name] varchar(20), Period varchar(20))

create table #Assets (AssetId varchar(20), [Name] varchar(20))

insert into #City values (1, 'Asset1', null)

insert into #City values (2, 'Asset2', null)

insert into #City values (3, 'Asset3', null)

insert into #Assets values (1, 'Asset1')

insert into #Assets values (2, 'Asset1')

insert into #Assets values (3, 'Asset1')

insert into #Assets values (4, 'Asset2')

insert into #Assets values (5, 'Asset2')

insert into #Assets values (6, 'Asset3')

insert into #Assets values (7, 'Asset3')

select * from #City

select * from #Assets

update #City

set Period = (

select top 1 a.AssetID

from #Assets a

where #City.Name = a.Name

order by a.AssetId asc)

select * from #City

drop table #City

drop table #Assets

Thank you!

Re: Convert Update SQL statement to proc sql

ChrisNZ — Wed, 07 Oct 2020 08:46:45 GMT

If you remove the octothorpes and add semicolons, you should be fairly close to valid SAS SQL code.

Re: Convert Update SQL statement to proc sql

ChrisNZ — Thu, 08 Oct 2020 05:19:14 GMT

Hello?

Re: Convert Update SQL statement to proc sql

Ja5ya — Thu, 08 Oct 2020 07:39:38 GMT

I did try it, but SAS gives out error for the TOP 1 statement, so I'm still stuck.

Re: Convert Update SQL statement to proc sql

ChrisNZ — Thu, 08 Oct 2020 07:48:13 GMT

Show us

Re: Convert Update SQL statement to proc sql

Ja5ya — Thu, 08 Oct 2020 09:00:01 GMT

proc sql;

update City
set Period = (
select top 1 a.AssetID
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, (, *, **, +, ',', -, '.', /, <, <=, <>, =,
>, >=, ?, AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE,
LEN, LENGTH, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.

ERROR 200-322: The symbol is not recognized and will be ignored.
from Assets a
where City.Name = a.Name
order by a.AssetId asc);

quit;

Re: Convert Update SQL statement to proc sql

ChrisNZ — Thu, 08 Oct 2020 20:44:09 GMT

This is not valid ANSI SQL. Only SQL Server accepts this syntax.

Try something like this:

data T; 
  set SASHELP .CLASS;
proc sql;
  update T
  set AGE = (
    select max(WEIGHT)
    from SASHELP.CLASS 
    where T.AGE = CLASS.AGE 
  ) where T.AGE>14;

Re: Convert Update SQL statement to proc sql

Ja5ya — Fri, 09 Oct 2020 04:55:52 GMT

It didn't work as I am getting only one distinct value of weight when max function is used while top 1 produces multiple distinct values

Re: Convert Update SQL statement to proc sql

ChrisNZ — Fri, 16 Oct 2020 06:24:44 GMT

Change the code accordingly then. My code was an example of a valid query, I am unsure what you are after.

Just explaining that different SQL engines use different syntaxes, and you have to adapt your code accordingly.

> top 1 produces multiple distinct values

Does it? Select top 1 limits the number of returned records to one.

Re: Convert Update SQL statement to proc sql

Ja5ya — Fri, 16 Oct 2020 07:23:48 GMT

Sure, I'll keep trying. The result I got from max wasn't what I was expecting so it didn't work out.