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!
If you remove the octothorpes and add semicolons, you should be fairly close to valid SAS SQL code.
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;
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.