topic Re: SQLSERVER to SAS in SAS Programming

SQLSERVER to SAS

samanvi — Wed, 10 Feb 2021 19:09:26 GMT

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.

Re: SQLSERVER to SAS

Shmuel — Wed, 10 Feb 2021 19:32:24 GMT

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.

Re: SQLSERVER to SAS

SASKiwi — Wed, 10 Feb 2021 20:34:34 GMT

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;

Re: SQLSERVER to SAS

samanvi — Thu, 11 Feb 2021 17:16:25 GMT

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

Re: SQLSERVER to SAS

SASKiwi — Thu, 11 Feb 2021 19:16:33 GMT

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;

Re: SQLSERVER to SAS

samanvi — Fri, 12 Feb 2021 11:18:49 GMT

Thank you.