SQLSERVER to SAS

[samanvi]

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.

[Shmuel]

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.

[SASKiwi]

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; 

[samanvi]

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

[SASKiwi · Accepted solution]

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; 

[samanvi]

Thank you.