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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: