Hi Every one,
Could you help in converting the code .
we got requirement that need to convert the sqlserver code to the SAS.
There are 100 records in table abc and b.
declare @counter int set @counter=1 while @counter>0 begin begin select * into #a from abc; end; begin update x set y=m from #a x inner join b y on x.id=i.id; end; begin drop table #a end; select @counter=@@rowcount end
Thank you.
Sure, try adding this:
proc sql noprint;
connect using SQLSRVR;
execute (declare @counter int
set @counter=1
while @counter>0
begin
begin
select * into #a from abc;
end;
begin
update x set y=m from #a x inner join b y on x.id=i.id;
end;
begin
drop table #a
end;
select @counter=@@rowcount
end) by SQLSRVR;
create table MySASTable as
select * from connection to SQLSRVR
(select * from tempdb.x);
quit;
Not every sas programer knows sqlserver .
It could help if you describe the function that code runs:
it seems there are two inputs A and B and code executes an inner join.
I can't guess more.
If you are not moving your data out of SQL Server then the best option is simply to wrap the SQL Server SQL unchanged into a SAS SQL passthru query.
libname SQLSRVR odbc noprompt = "<SQL Server connection string>" DATABASE = MySQLServerDatabase;
proc sql noprint;
connect using SQLSRVR;
execute (declare @counter int
set @counter=1
while @counter>0
begin
begin
select * into #a from abc;
end;
begin
update x set y=m from #a x inner join b y on x.id=i.id;
end;
begin
drop table #a
end;
select @counter=@@rowcount
end) by SQLSRVR;
quit;
Thank you it worked for me. Is there a way to pull output table x? as x is an temporary table. I have read permission in sqlserver only. Is there a way to pull output data(table x) make to the sas dataset
Sure, try adding this:
proc sql noprint;
connect using SQLSRVR;
execute (declare @counter int
set @counter=1
while @counter>0
begin
begin
select * into #a from abc;
end;
begin
update x set y=m from #a x inner join b y on x.id=i.id;
end;
begin
drop table #a
end;
select @counter=@@rowcount
end) by SQLSRVR;
create table MySASTable as
select * from connection to SQLSRVR
(select * from tempdb.x);
quit;
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.