Convert Update SQL statement to proc sql

[Ja5ya]

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!

[ChrisNZ]

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

[ChrisNZ]

Hello?

[Ja5ya]

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

[ChrisNZ]

Show us

[Ja5ya]

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;

[ChrisNZ]

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;

[Ja5ya]

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

[ChrisNZ]

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. 

[Ja5ya]

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