BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
samanvi
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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; 

View solution in original post

5 REPLIES 5
Shmuel
Garnet | Level 18

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
PROC Star

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
Obsidian | Level 7

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
PROC Star

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
Obsidian | Level 7
Thank you.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 660 views
  • 2 likes
  • 3 in conversation